row_number() Group by?

sql-server tsql unique row-number
user1582928 picture user1582928 · Sep 20, 2012 · Viewed 89.9k times · Source

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

Answer

G.S picture G.S · Sep 21, 2012

Here is alternate 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

Related questions

How to select only the first rows for each unique value of a column

Let's say I have a table of customer addresses: CName | AddressLine ------------------------------- John Smith | 123 Nowheresville Jane Doe | 456 Evergreen Terrace John Smith | 999 Somewhereelse Joe Bloggs | 1 Second Ave In the table, one customer like John Smith can have multiple addresses. I need …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More