I have below requirement.
I have many columns in the table and want to generate a sequence number using only one column.
I am not able to use RANK() as I am using GROUP BY in my SQL.
Is there any other way to achieve this:
Account - ID
12345 - 9
12345 - 8
98765 - 6
98765 - 4
Account - ID - SeqNum
12345 - 9 - 1
12345 - 8 - 1
98765 - 6 - 2
98765 - 4 - 2
I want to generate sequence number using Account column as said in the example.
Thanks a lot in advance.
You can't use OLAP and aggregates together. The workaround is to use derived table or temporary table. Try this:take your group by query inside. Rank the result from obtained from derived.
I can't try now for you, since my TD is down. I have to download again.
of course you can use RANK and aggregates in the same query, OLAP functions are calculated after GROUP BY/HAVING.
You probably use the deprecated RANK(col) instead of RANK() OVER (ORDER BY col DESC):
select col, sum(col2) as sumcol2, rank() over (order by sumcol2)
group by 1;
Oops, it is me who is with the older generation code. I used the deprecated one.
I dont know from which version, this one supports. Thanks Dieter.
Hi Sagar ,
Please find the query below . Hope it helps .
FROM TableA X
( SEL Account1 ,ROW_NUMBER() OVER ( ORDER BY Account1 ) COL3
GROUP BY 1 ) A
X.Account1 = A.Account1;