row_number() Group by?

Question

I have a data set that contains the columns Date, Cat, and QTY. What I want to do is add a unique column that only counts unique Cat values when it does the row count. This is what I want my result set to look like:

enter image description here

By using the SQL query below, I'm able to get row using the row_number() function.

However, I can't get that unique column that I have depicted above. When I add group by to the OVER clause, it does not work. Does anybody have any ideas as how I could get this unique count column to work?

SELECT 
   Date,    
   ROW_NUMBER() OVER (PARTITION BY Date ORDER By Date, Cat) as ROW,
   Cat,
   Qty
FROM SOURCE

rogerdpack · Accepted Answer · 2021-10-22 16:57:58Z

53

Here is a solution.

You need not worry about the ordering of Cat. Using following SQL you will be able to get unique values for your Date & Cat combination.

SELECT 
   Date,    
   ROW_NUMBER() OVER (PARTITION BY Date, Cat ORDER By Date, Cat) as ROW,
   Cat,
   Qty
FROM SOURCE

edited Oct 22, 2021 at 16:57

rogerdpack

67.7k40 gold badges290 silver badges409 bronze badges

answered Sep 21, 2012 at 10:42

G.S

7772 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nahuel Fouilleul · Accepted Answer · 2012-09-20 18:46:31Z

25

DENSE_RANK() OVER (PARTITION BY date ORDER BY cat)

edited Sep 20, 2012 at 18:46

answered Sep 20, 2012 at 18:35

Nahuel Fouilleul

19.5k2 gold badges36 silver badges37 bronze badges

3 Comments

user1582928 Over a year ago

Thanks, this is a step in the right direction. However, the fact is that my actual data set is not ordered by cat. Do you know how it would work in that example? (Imagine that in my cat column, DEF comes before ABC. But it is true that DEF values are all consecutive in my ordering so you wouldnt see DEF, DEF, ABC, ABC, DEF as values in the cat column)

prashanth Over a year ago

@user1582928 As per msdn.microsoft.com/en-us/library/ms173825.aspx "<order_by_clause> Determines the order in which the DENSE_RANK function is applied to the rows in a partition". And you can always add ORDER BY at the end of SELECT.

Nahuel Fouilleul Over a year ago

@user1582928, if you want you can specify a function to change order, for example: order by decode('DEF',1,'ABC',2,3)

Collectives™ on Stack Overflow

row_number() Group by?

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related