Row Number() Order Issue

Question

Apologies in advance if this specific scenario has been asked previously, but I can't seem to get these to order properly (which is probably from staring at it for too long).

I'm using Netezza/Oracle, and In the data set below - I basically need the order_num to result in 1,2,2,2,2,3,4 (basically grouping Department and Desc1 (desc1 is not unique as there are different codes for each year, but I'm only interested in the type, not year).) Among other attempts, I've tried:

row_number () over (partition by a.department order by desc1) order_num

Which orders it alphabetically. I've also ordered by seq_no and desc1 - but that only works if I needed it alphabetically.

Thanks in advance.

Answer 1

Assuming that the Country is consistent with the grouping as you have shown; if you get the minimum seq_no per country in either a CTE or sub-query you can use this value in your dense_rank function, e.g.

    SELECT 
       m.Department,
       m.Desc1,
       m.seq_no,
       m.Country,
       m.beg_date,
       m.end_date,
       dense_rank() OVER(PARTITION BY m.Department ORDER BY mintbl.MinSeq) 
FROM dbo.mytable AS m
JOIN ( SELECT min(m.seq_no) AS MinSeq,
               m.Department,
                         m.Country
        FROM dbo.mytable AS m
        GROUP BY m.Department,m.Country
     ) AS mintbl ON mintbl.Department = m.Department AND mintbl.Country = m.Country
ORDER BY m.seq_no