GROUP BY Query Help
-
Hello Everyone
I am in need of a little help with a query. I would like to select the Max RowID per date. Or the last date per RowID. Group the data by the Birthdate, List everyone with that particular Birthdate
So for one record, there would be RowID = 4, Birthdate = 1900-1-20, FirstName = Frank, COUNT of the number of people with that Birthdate = 4
CREATE TABLE [dbo].[Birthdate](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[DateID] [int] NOT NULL,
[Birthdate] [datetime] NOT NULL,
[FirstName] [varchar](20) NOT NULL
)
Add some data:
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
,'Joe'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
, 'Matt'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
, 'Manny'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
, 'Frank'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-18'
, 'Sally'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-18'
, 'Jan'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-18'
, 'Jan'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-18'
, 'Sandy'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-18'
, 'Jenny'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-17'
, 'Mike'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-17'
, 'Jimmy'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-17'
, 'Angie'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-17'
, 'Lucy'
)
Hope that makes sense. This has to be pretty simple, but I am missing something.
Thanks in advance for your help
Andrew SQLDBA
-
Andrew, how about this following piece of code??
;WITH CTE(Birthdate, DATE_COUNT)
AS
(
SELECT Birthdate, COUNT(Birthdate) FROM [dbo].[Birthdate] GROUP BY Birthdate
)
SELECT
BD.ROWID,BD.Birthdate, BD.FIRSTNAME, CTE.DATE_COUNT
FROM
[dbo].[Birthdate] BD
LEFT JOIN
CTE CTE
ON
CTE.Birthdate = BD.Birthdate
Check and tell us if thats what u needed!!
Cheers!!
-
Sorry, Not exactly. I need only three rows returned. There are only 3 distinct dates. I need the MaxRowID for each of the dates, that persons name, and then a count of all the people with that particular birthdate.
For example:
RowID = 4, Birthdate = 1900-1-20, FirstName = Frank, Count = 4
Meaning there are 4 people with that birthdate. I am more looking to select the last record that was inserted, that by using the RowID.
Thanks
Andrew SQLDBA
-
My Mistake
My data that I gave you was incorrect. I have modified the DateID
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
, 'Joe'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
, 'Matt'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
, 'Manny'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
1
, '1900-01-20'
, 'Frank'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
2
, '1900-01-18'
, 'Sally'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
2
, '1900-01-18'
, 'Jan'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
2
, '1900-01-18'
, 'Jan'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
2
, '1900-01-18'
, 'Sandy'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
3
, '1900-01-18'
, 'Jenny'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
3
, '1900-01-17'
, 'Mike'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
3
, '1900-01-17'
, 'Jimmy'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
3
, '1900-01-17'
, 'Angie'
)
INSERT INTO Birthdate
(
DateID
, Birthdate
, FirstName
)
VALUES
(
3
, '1900-01-17'
, 'Lucy'
)
Thanks
Andrew SQLDBA
-
This is really close, but when I add in the FirstName column, the query returns all records. I want only the name associated with the Max(ROWID)
SELECT Birthdate, DateID,MAX(RowID) AS RowID, COUNT(Birthdate) AS NumberOfBDs
FROM Birthdate
GROUP BY Birthdate,DateID
ORDER BY Birthdate
Thanks
Andrew SQLDBA
-
Hmm, that would involve a minor tweak in the CTE , andrew... here is the tweaked code..
;WITH CTE(Birthdate,ROWID, DATE_COUNT)AS
(
SELECT Birthdate, MAX(ROWID) , COUNT(Birthdate) FROM [dbo].[Birthdate] GROUP BY Birthdate
)
SELECT
BD.ROWID,BD.Birthdate, BD.FIRSTNAME, CTE.DATE_COUNT
FROM
[dbo].[Birthdate] BD
INNER JOIN
CTE CTE
ON
CTE.Birthdate = BD.Birthdate
AND
CTE.ROWID = BD.ROWID
This returns only 3 rows in my PC.. Hope this what u wnated!!
Cheers!!
-
Yes, that is perfect. I was racking my little brain on this one.
Hope that you don't mind if I take the code apart and figure out how it works, and what it does
Thank you for your help
Andrew SQLDBA
-
AndrewSQLDBA (4/22/2010)
I want only the name associated with the Max(ROWID)Check out the last post of mine, you will get only the last inserted row for a date, the date, the name associated with the row and the count of people born on that date. Hope that is what you wanted...
Include an "ORDER BY" clause in the end and your result set will be exactly the way u wanted 🙂
Tell us if that code worked for you.
Cheers!!
-
AndrewSQLDBA (4/22/2010)
Hope that you don't mind if I take the code apart and figure out how it works, and what it doesAt your will, brother!
1. The CTE will collect the MAX (ROWID) and the count of rowid for a particular date ; this produces the rowid, count and the date associated with them
2.Upon "joining" the CTE with the real table, will fetch you the name associated wiht the rowid.
3. combine step 1 and step 2, you are on the right road !! 🙂
Cheers!!
C'est Pras!
-
Try this
WITH CTE AS (
SELECT RowID,
DateID,
Birthdate,
FirstName,
ROW_NUMBER() OVER(PARTITION BY Birthdate ORDER BY RowID DESC) AS rn,
COUNT(*) OVER(PARTITION BY Birthdate) AS [COUNT]
FROM Birthdate)
SELECT RowID,Birthdate,FirstName,[COUNT]
FROM CTE
WHERE rn=1
ORDER BY RowID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
AndrewSQLDBA (4/22/2010)
Hope that you don't mind if I take the code apart and figure out how it works, and what it doesActually, I think you will find that most people here will tell you not only to do this, but also to never put any code that you get off of the internet anywhere near a production system until you have figured out how it works and what it does.
If you have any questions about the "how it works/what it does", just ask away.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply