Query on GroupBy using Row_Number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query on GroupBy using Row_Number

Author

Topic

nash_Sql
Starting Member

6 Posts

Posted - 2009-09-17 : 10:56:21

My table data is as below where i have 'GroupId; column.Each GroupId has mulitple rows. Some rows have 'GroupId' as null.

I want to fecth top 1 row for from each Group whoe GroupId are not null and all rows from Group whose GroupId is null.
Want to achieve both in single SQL Query (no Stored procedure or anything else).I have writen my query which doesn't first part of my
requirement and i want my query to modified to achieve second part as well.

ID | GroupId | Level | LoginTime
F5ADCC1C-6340-42D3-9F84-9F7ECA2D737A |C1EC4185-4E49-4AD7-B00D-A9C847986188 | 1 | 2009-09-09 09:09:09.000
7F250E29-C46C-4C8E-B1D2-4BC9BFBD41E8 | NULL | 1 | 2009-09-09 09:09:09.000
AF59C8E1-A30C-4C74-B6E2-87A31329CD3D | NULL 1 | 2009-09-09 09:09:09.000
A9ADCC1C-6340-42D3-9F84-9F7ECA2D737D | DB5F1ED4-650D-4143-8752-0EA618E2A17E | 3 | 2009-09-09 09:09:09.000
4CB6609B-4781-4904-A66F-268F855BB3E9 | C1EC4185-4E49-4AD7-B00D-A9C847986188 | 2 | 2009-09-09 09:09:09.000
659DD185-6BF9-41BA-9B2D-B3962B970DEC | DB5F1ED4-650D-4143-8752-0EA618E2A17E | 1 | 2009-09-09 09:09:09.000

SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER ( PARTITION BY [Employee].[GroupId] ORDER BY [Employee].[Level] ASC) AS [RowNumber]
FROM Employee
WHERE [Employee].[LoginTime] <= '2010-10-09 09:09:09.000' AND ( [Employee].[Level] = '0' OR [Employee].[Level] = '1' )
) AS Employee WHERE [RowNumber] = '1'

Above Query result me (no need to give importance to OrderBy and Where clause condition) -

ID | GroupId | Level | LoginTime

7F250E29-C46C-4C8E-B1D2-4BC9BFBD41E8 | NULL | 1 | 2009-09-09 09:09:09.000
659DD185-6BF9-41BA-9B2D-B3962B970DEC | DB5F1ED4-650D-4143-8752-0EA618E2A17E | 1 | 2009-09-09 09:09:09.000
F5ADCC1C-6340-42D3-9F84-9F7ECA2D737A | C1EC4185-4E49-4AD7-B00D-A9C847986188 | 1 | 2009-09-09 09:09:09.000

My above query is grouping rows by 'GroupId' and fecthing top 1 row from each group which is fine.
Along with that i want to to have all rows (not just top 1 row) whose groupId is null.
Currently above query is getting only top one row whose 'GroupId' is null along with for other GroupId.

I need to help in modifying existing query or any new query which would help me to get desired result.
I am looking to achieve this using only query (no Stored procedure or anything else).

Thanks,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 11:03:45

SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER ( PARTITION BY [Employee].[GroupId] ORDER BY [Employee].[Level] ASC) AS [RowNumber]
FROM Employee
WHERE [Employee].[LoginTime] <= '2010-10-09 09:09:09.000'
AND ( [Employee].[Level] = '0' OR [Employee].[Level] = '1')
and GroupId is not null
union all
SELECT *,1 AS [RowNumber]
FROM Employee
WHERE [Employee].[LoginTime] <= '2010-10-09 09:09:09.000'
AND ( [Employee].[Level] = '0' OR [Employee].[Level] = '1')
and GroupId is null
) AS Employee WHERE [RowNumber] = '1'

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 02:44:08

You dont need single quotes around 1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources