I want a T-SQL procedure or sql query to perform the following action:

Question

I have a table with these rows:

City     | Day
NEWYORK  | MON
NEWYORK  | MON
NEWYORK  | MON
NEWYORK  | TUE
CHICAGO  | MON
CHICAGO  | MON

I need to write a query to add column Occurrence to the result using data from Columns City and Day:

City     | Day    | Occurrence
NEWYORK  | MON    |   1
NEWYORK  | MON    |   2
NEWYORK  | MON    |   3
NEWYORK  | TUE    |   1
CHICAGO  | MON    |   1
CHICAGO  | MON    |   2

EG: For NEWYORK if MON occurs 1st then Occurrence=1, if MON occurs twice then corresponding Occurrence=2 and so on.

column names are City,Day and Occurence. 6 rows and 3 columns — Sambit Pradhan, Jun 18 '17 at 19:29
All the SQL dialects are very Different. Also so How your Result should looks like — Jens, Jun 18 '17 at 19:37
actually the 3rd column is not present in the table i need to calculate it using 1st two columns. So thats my result — Sambit Pradhan, Jun 18 '17 at 19:38

score 2 · Answer 1 · edited Jun 18 '17 at 21:36

2

The row_number window function should do exactly what you're looking for:

SELECT [city], [day], ROW_NUMBER() OVER (PARTITION BY [city], [day] ORDER BY 1) AS [occurence]
FROM   my_table;

edited Jun 18 '17 at 21:36

Dan Guzman

43,250
3
46
71

answered Jun 18 '17 at 19:50

Mureinik

297,002
52
306
350

I want a T-SQL procedure or sql query to perform the following action:

1 Answers1