More MAX function problems - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

More MAX function problems

Author

Topic

Jamhin4
Starting Member

4 Posts

Posted - 2010-02-10 : 14:50:30

I am needing to pull only rows with the most recent dates from two joined tables. I did some reading last night on the use of the MAX function and am getting closer, but I'm still getting back more rows on some facilites and the really scary thing is I'm only getting 23 rows back and should get more like 250. Could someone take a look at my query and the results and show me where I'm going wrong? (Please note FacilityNumber 30009, I only want the one row with the most current date. And again, a major concern is why I would only get 23 records out of 250.)

HUGE THANKS!!!
Jamie
p.s. Tried to format things below so they'll be easier to read, but I'm sure it'll all go haywire when I submit it ...

SELECT fa.FacilityNumber, fa.StaffID, fa.StartDate, dbo.tblStaff.FirstName, dbo.tblStaff.LastName
FROM dbo.tblFacilityAssignment AS fa
INNER JOIN
(SELECT MAX(StartDate) AS maxstartdate, StaffID
FROM dbo.tblFacilityAssignment
GROUP BY StaffID) AS maxresults ON
fa.StaffID = maxresults.StaffID AND
fa.StartDate = maxresults.maxstartdate INNER JOIN
dbo.tblStaff ON fa.StaffID = dbo.tblStaff.StaffID
ORDER BY fa.FacilityNumber

FacilityNumber StaffID StartDate FirstName LastName
30001 58 02/08/2010 12:00:00 AM Cindy Pyle
30009 59 01/11/2010 12:00:00 AM Micca Burris
30009 58 02/08/2010 12:00:00 AM Cindy Pyle
30012 115 10/01/2009 12:00:00 AM Ann Rochell
30015 58 02/08/2010 12:00:00 AM Cindy Pyle
30033 131 10/14/2009 12:00:00 AM Charles Arnold
30034 178 01/08/2010 12:00:00 AM Michael Johnson
30036 71 10/19/2009 12:00:00 AM Rebecca Dixon
30068 93 10/30/2009 12:00:00 AM Delois Calhoun
30072 63 10/28/2009 12:00:00 AM Carolyn Walker
30088 96 11/02/2009 12:00:00 AM Craig Bodurtha
30097 171 01/16/2010 12:00:00 AM Nikki Banks
30124 57 01/11/2010 12:00:00 AM Kelli Hilburn
30144 178 01/08/2010 12:00:00 AM Michael Johnson
30190 171 01/16/2010 12:00:00 AM Nikki Banks
30315 114 02/04/2010 12:00:00 AM Ann Glover
30324 54 01/23/2010 12:00:00 AM Jason Ham
30327 57 01/11/2010 12:00:00 AM Kelli Hilburn
30338 53 02/04/2010 12:00:00 AM Christine Dollard
30339 141 02/04/2010 12:00:00 AM Brent Patterson
30340 60 02/09/2010 12:00:00 AM Connie Currie
30342 60 02/09/2010 12:00:00 AM Connie Currie
30343 142 02/09/2010 12:00:00 AM Jamie Hiner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:53:38

as per your sample data it should be


SELECT fa.FacilityNumber, fa.StaffID, fa.StartDate, dbo.tblStaff.FirstName, dbo.tblStaff.LastName
FROM dbo.tblFacilityAssignment AS fa 
INNER JOIN
(SELECT MAX(StartDate) AS maxstartdate, FacilityNumber
FROM dbo.tblFacilityAssignment
GROUP BY FacilityNumber) AS maxresults ON 
fa.FacilityNumber = maxresults.FacilityNumber AND 
fa.StartDate = maxresults.maxstartdate INNER JOIN
dbo.tblStaff ON fa.StaffID = dbo.tblStaff.StaffID
ORDER BY fa.FacilityNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:54:33

are you sure every record in tblStaff will have an entry in tblFacilityAssignment? if not you need to start from tblStaff and take left join with tblFacilityAssignment

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jamhin4
Starting Member

4 Posts

Posted - 2010-02-10 : 15:14:26

visakh16, I took your query, and ran it and I'm feeling a lot happier. Got back 280 rows, which is way closer to 250 than 23! But now I have more duplicates showing up, but that's a data problem, not an issue with the query. There are duped startdates in the system, which is a known problem with our application right now.

Sample of top 10 rows below (cleared up my problem with 30009) - THANKS!!!!!!!! You guys ROCK. I just joined the forum last night and you guys have already helped me SO much. Hope I can return the favor one day. ;-) Still have much to learn to catch up with you guys though.

facility id startdate firstname lastname
30001 58 2010-02-08 00:00:00.000 Cindy Pyle
30002 57 2009-10-13 00:00:00.000 Kelli Hilburn
30003 58 2009-11-23 00:00:00.000 Cindy Pyle
30003 60 2009-11-23 00:00:00.000 Connie Currie
30004 60 2010-01-11 00:00:00.000 Connie Currie
30005 142 2010-02-05 00:00:00.000 Jamie Hiner
30008 57 2009-10-19 00:00:00.000 Kelli Hilburn
30009 58 2010-02-08 00:00:00.000 Cindy Pyle
30010 53 2009-11-11 00:00:00.000 Christine Dollard
30010 114 2009-11-11 00:00:00.000 Ann Glover

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 00:06:16

Great
You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources