Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select the first records of each group Expand / Collapse
Author
Message
Posted Wednesday, April 7, 2010 12:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:18 PM
Points: 1,773, Visits: 3,207
Hello,

I have a query that is using group by. I then want the first records of each group.

I know in Access I can use first to have the first records. but in Sql server I cannot.

The Min doesn't work as I expected to do. If there is only one min, I think it will work. But now I want two fields.
This will pull wrong records.

Please help.

See below query:
SELECT TOP (100) PERCENT [Last Name], [First Name], [Building Desc], MIN([Sub Plan Desc]) AS Expr1, MIN([Sub Plan Cat Desc]) AS Expr2
FROM dbo.Benefit
GROUP BY [Last Name], [First Name], [Building Desc]
ORDER BY [Last Name], [First Name], [Building Desc], MIN([Sub Plan Desc]), MIN([Sub Plan Cat Desc])
Post #898884
Posted Wednesday, April 7, 2010 1:00 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
In SQL 2005 and later, I usually use Row_Number and a CTE to do that.

Something like this:

;
WITH CTE
AS (SELECT [Last Name],
[First Name],
[Building Desc],
[Sub Plan Desc],
[Sub Plan Cat Desc],
ROW_NUMBER() OVER (PARTITION BY [Last Name],
[First Name], [Building Desc] ORDER BY [Sub Plan Desc], [Sub Plan Cat Desc]) AS Row
FROM dbo.Benefit)
SELECT [Last Name],
[First Name],
[Building Desc],
[Sub Plan Desc],
[Sub Plan Cat Desc]
FROM CTE
WHERE Row = 1 ;



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #898918
Posted Wednesday, April 7, 2010 1:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:18 PM
Points: 1,773, Visits: 3,207
Thanks,
but I still get multiple records for each person.

I only want the first records
Post #898924
Posted Wednesday, April 7, 2010 1:29 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Do you have a "Person ID" or something of that sort, or just first and last name?

If it's just by name, then you can change the Partition By portion of the Row_Number function so it just gets each person once.

(If it's just by name, please consider redesigning the database, if you're in a position to do so. Otherwise, the moment you have two "John Smith"s in the database, all kinds of things will break.)


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #898931
Posted Thursday, April 8, 2010 8:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
Ann Cao (4/7/2010)
Thanks,
but I still get multiple records for each person.

I only want the first records


Based on your original query you would expect to get multiple rows per person if they have been in/assigned to more than 1 building. If you want only 1 row per person then you would need to eliminate [building desc] from the Group By/Partition By.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #899653
Posted Thursday, April 8, 2010 9:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:18 PM
Points: 1,773, Visits: 3,207
Thanks.


This is actually not a table, just an excel file from a staff. I imported into the sql server to do the data manipulation.

I am able to do it by using the following queries, it works.

SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY dbo.Benefits.[Last Name], dbo.Benefits.[First Name],
dbo.Benefits.[Building Desc]
ORDER BY dbo.Benefits.[Sub Plan Desc], dbo.Benefits.[Sub Plan Cat Desc]) AS Seq,
dbo.Benefits.[Last Name], dbo.Benefits.[First Name], dbo.Benefits.[Building Desc],
dbo.Benefits.[Sub Plan Desc], dbo.Benefits.[Sub Plan Cat Desc]
FROM dbo.Benefits) t
WHERE Seq = 1

Post #899692
Posted Thursday, April 8, 2010 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
I think logically that Gus's query using a CTE and your query using the derived table are the same.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #899701
Posted Thursday, April 8, 2010 9:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:18 PM
Points: 1,773, Visits: 3,207
I tried you query again, it works this time.

Thank you!
Post #899704
Posted Friday, April 9, 2010 6:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Jack Corbett (4/8/2010)
I think logically that Gus's query using a CTE and your query using the derived table are the same.


They are.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #900413
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse