Query to bring back a record with the highest date from a column

Question

Post reply

Query to bring back a record with the highest date from a column

Debbie Edwards

SSCrazy Eights

Points: 9217

January 26, 2007 at 7:24 am

#171187

Hi all,

I have created a query that brings back the following information from tables which puts an end column in where the pupil has more than 1 attendance at a school COUNT(*). This means that I have put in a having clause where this count > 1

ID	PUP_UPN_DFEE	PUP_UPN_DFEE_TEMP	F_NAME	M_NAME	S_NAME	PER_DOB	SCH_DFEE	SCH_NAME	PAT_FROM_DATE	PAT_TO_DATE	More_Than_1_Attendace
108	N830		Sara		Smith	12/06/1991	Y152	Old Hill school	08/09/1999	31/08/2002	2
108	N830		Sara		Smith	12/06/1991	Y152	Old Hill school	06/01/2003	31/08/2003	2
142	U830		Tracey	Rose	Austin	31/06/1993	Y152	Old Hill school	05/09/2000	31/08/2002	2
142	U830		Tracey	Rose	Austin	31/06/1993	Y152	Old Hill school	16/06/2004	31/08/2004	2

The above is just a sample of two pupils. I then want to run a query that only brings back the record with the highest PAT_TO_DATE.

For example Sara Smith, the second record is the one that will be brought back.

I tried a MAX function but couldnt quite get it to work.

any help would be fantastic.

Debbie

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

Something along the lines of the following should work:

SELECT Y.*

FROM YourTable Y

JOIN (

SELECT Y1.[ID], MAX(Y1.PAT_TO_DATE) AS PAT_TO_DATE

FROM YourTable Y1

GROUP BY Y1.[ID]) D

ON Y.[ID] = D.[ID] AND Y.PAT_TO_DATE = D.PAT_TO_DATE

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

I cannot thank you enough. Its worked perfectly. Although now I need to sort out all those records where there is an open attendance (WHERE PAT_TO_DATE IS NULL) which should be fun.

I was looking at this in completely the trying way trying to put the MAX in a having clause.

Thanks again

Debbie

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 3

I cannot thank you enough. Its worked perfectly. Although now I need to sort out all those records where there is an open attendance (WHERE PAT_TO_DATE IS NULL) which should be fun.

I was looking at this in completely the trying way trying to put the MAX in a having clause.

Thanks again

Debbie

Rob Farley SSCrazy Points: 2739 More actions · Answer 4

I like the OVER clause for this type of problem.

select * from

(SELECT *, RANK(PARTITION BY PUP_UPN_DFEE ORDER BY PUP_UPN_DFEE, PAT_TO_DATE DESC) as rn

FROM TableName

) t

where rn = 1

Or you can use ROW_NUMBER instead of RANK if you want to make sure you only ever get one row per student.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 5

Thanks for that,

It looks a bit scary,but I will definitely give it a go!

Debbie