Generating Sequence Number when GROUP BY in the Query

Database
Enthusiast

Generating Sequence Number when GROUP BY in the Query

Hi,

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:

Input:-

TableA:-

Account - ID

12345 - 9

12345 - 8

98765 - 6

98765 - 4

Expected output:-

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.

Sagar.

5 REPLIES
Enthusiast

Re: Generating Sequence Number when GROUP BY in the Query

Hi Pavan,

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.

Cheers,

Raja

Junior Contributor

Re: Generating Sequence Number when GROUP BY in the Query

Hi Sagar,

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) 
from tab
group by 1;
Enthusiast

Re: Generating Sequence Number when GROUP BY in the Query

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.

Enthusiast

Re: Generating Sequence Number when GROUP BY in the Query

Thanks a lot Dieter and Raja for your inputs.

Enthusiast

Re: Generating Sequence Number when GROUP BY in the Query

Hi Sagar ,

Please find the query below . Hope it helps .

SEL

X.Account1 ,

ID1 ,

A.COL3

FROM TableA X

INNER JOIN

( SEL Account1 ,ROW_NUMBER() OVER ( ORDER BY Account1 ) COL3

FROM TableA

GROUP BY 1 ) A

ON

X.Account1 = A.Account1;