SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select the first records of each group


select the first records of each group

Author
Message
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3895 Visits: 4025
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])
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23917 Visits: 9730
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
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3895 Visits: 4025
Thanks,
but I still get multiple records for each person.

I only want the first records
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23917 Visits: 9730
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
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18902 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3895 Visits: 4025
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
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18902 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3895 Visits: 4025
I tried you query again, it works this time.

Thank you!
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23917 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search