CODEWITHSUNDEEP
sql-servert-sqlpartition
user1896017
user1896017

Reputation: 83

Use tsql partition by twice in one query

I have a table with fields of group 1, group 2 and date.

If I run this query I get the result below:

SELECT 
   [group1]
  ,[group2]
  ,[date]
  ,ROW_NUMBER() over (PARTITION by [group1] order by [date]) as ord
FROM [dev_DP].[dbo].[myTable]

+--------+--------+-------------------------+-----+
| group1 | group2 |          date           | ord |
+--------+--------+-------------------------+-----+
| dan    | alarm  | 2014-05-14 12:00:00.000 |   1 |
| dan    | snooze | 2014-05-14 12:30:00.000 |   2 |
| dan    | snooze | 2014-05-14 12:50:00.000 |   3 |
| jim    | snooze | 2014-05-14 11:00:00.000 |   1 |
| jim    | snooze | 2014-05-14 12:45:00.000 |   2 |
| jim    | alarm  | 2014-05-14 13:45:00.000 |   3 |
+--------+--------+-------------------------+-----+

I want to add another column which then adds a row number to the table based on the group 1 and group 2 field but retains the date order just for group 1. The results I want are below:

+--------+--------+---------------------+-----+------+
| group1 | group2 |        date         | ord | ord2 |
+--------+--------+---------------------+-----+------+
| dan    | alarm  | 2014-05-14 12:00:00 |   1 |    1 |
| dan    | snooze | 2014-05-14 12:30:00 |   2 |    1 |
| dan    | snooze | 2014-05-14 12:50:00 |   3 |    2 |
| jim    | snooze | 2014-05-14 11:00:00 |   1 |    1 |
| jim    | snooze | 2014-05-14 12:45:00 |   2 |    2 |
| jim    | alarm  | 2014-05-14 13:45:00 |   3 |    1 |
+--------+--------+---------------------+-----+------+

How can I do this?

Thanks Dan

Upvotes: 1

Views: 1708

Answers (2)

user1896017
user1896017

Reputation: 83

If I switch those terms around:

  SELECT 
  [group1]
  ,[group2]
  ,[date]

  ,ROW_NUMBER() over (PARTITION by [group1], [group2] order by [date]) as ord2
  ,ROW_NUMBER() over (PARTITION by [group1] order by [date]) as ord1
  FROM [dev_DP].[dbo].[myTable]

I thought this gives me the result I wanted but it doesn't...If I add another record you can see the error.

+--------+--------+-------------------------+------+------+
| group1 | group2 |          date           | ord2 | ord1 |
+--------+--------+-------------------------+------+------+
| dan    | alarm  | 2014-05-14 12:00:00.000 |    1 |    1 |
| dan    | snooze | 2014-05-14 12:30:00.000 |    1 |    2 |
| dan    | snooze | 2014-05-14 12:50:00.000 |    2 |    3 |
| dan    | alarm  | 2014-05-14 13:00:00.000 |    2 |    4 |
| jim    | snooze | 2014-05-14 11:00:00.000 |    1 |    1 |
| jim    | snooze | 2014-05-14 12:45:00.000 |    2 |    2 |
| jim    | alarm  | 2014-05-14 13:45:00.000 |    1 |    3 |
+--------+--------+-------------------------+------+------+

Upvotes: 0

Jerrad
Jerrad

Reputation: 5290

  SELECT 
  [group1]
  ,[group2]
  ,[date]
  ,ROW_NUMBER() over (PARTITION by [group1] order by [date]) as ord1
  ,ROW_NUMBER() over (PARTITION by [group1], [group2] order by [date]) as ord2
  FROM [dev_DP].[dbo].[myTable]

Upvotes: 1

Related Questions