ROWNUM with a condition

Question

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

Could you amend your question with an example of the outcome you are looking for? — cosinepenguin
You can easily achieve the output you are looking for using ROW_NUMBER() OVER (PARTITION BY Col_1, Col_2, Col_3 ORDER BY Col_4) — Giorgos Betsos
Hi Giorgos, thanks ! I am testing RANK(), ROW_NUMBER, DENSE_RANK but I completely missed out PARTITION BY. Once I added it, just works ... thanks. — user3015739
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 — user3015739

MT0 MT0 · Accepted Answer · 2017-06-29T08:44:33

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

ROWNUM with a condition

1 Answers