April 7, 2010 at 12:23 pm
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])
April 7, 2010 at 1:00 pm
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
April 7, 2010 at 1:10 pm
Thanks,
but I still get multiple records for each person.
I only want the first records
April 7, 2010 at 1:29 pm
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
April 8, 2010 at 8:52 am
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
Consultant - Straight Path Solutions
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
April 8, 2010 at 9:29 am
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
April 8, 2010 at 9:35 am
I think logically that Gus's query using a CTE and your query using the derived table are the same.
Jack Corbett
Consultant - Straight Path Solutions
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
April 8, 2010 at 9:36 am
I tried you query again, it works this time.
Thank you!
April 9, 2010 at 6:18 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply