CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user3015739
user3015739

Reputation: 643

ROWNUM with a condition

I am wondering if a select statement or view can be achieve to have a running number based on a condition

Col_1 Col_2 Col_3 Col_4 Col_5 20010887A DISP CT1 A030 K2772 1 20010887A DISP CT1 A040 K1444 2 20010887A DISP CT1 A050 K2422 3 20010887A DISP CT1 A060 K3430 4 20010887A DISP CT2 D115 K1473 1 20010887A DISP CT2 D120 K0053A 2 20010887A DISP CT2 D130 K1444 3 20010887A MIX CT1 F190 K1420 1 20010887A MIX CT2 F220 K1433 1 20010887A MIX CT3 G260 K2742A 1 20010887A MIX CT4 I310 K0916 1 20010887A MIX CT5 J330 K1258 1

The last column is the running number I wanted and it was grouped using Col_1, Col_2, Col_3, Col_5. Col_4 is just a sorting sequence.

SELECT COL_1, COL_2, COL_3, COL_4, COL_5, ROW_NUMBER() OVER ( PARTITION BY COL_1, COL_2, COL_3 ORDER BY COL_4) AS Col5_Count FROM myTable

Using the above query, is it possible to have Col_3 ROW_NUMBER counted the same way but in a distinct manner ? For example, all CT1 should have 1, CT2 should have 2.

Thanks

Upvotes: 0

Views: 678

Answers (1)

MT0
MT0

Reputation: 167774

is it possible to have Col_3 ROW_NUMBER counted the same way but in a distinct manner ? For example, all CT1 should have 1, CT2 should have 2.

You can use the DENSE_RANK() analytic function:

SELECT t.*,
       ROW_NUMBER() OVER ( PARTITION BY COL_1, COL_2, COL_3 ORDER BY COL_4 ) AS Col5_Count,
       DENSE_RANK() OVER ( ORDER BY COL_3 ) AS Col3_Rank
FROM   myTable t

Upvotes: 1

Related Questions