How to select a row that has most recent date of updation !

Question

How to select a row that has most recent date of updation !

Mounish

Old Hand

Points: 322
More actions
January 6, 2011 at 12:51 pm

#243239

I have a table like this,
SID[Varchar] GPA[Varchar] DATE_OF_UPDATE[Date]
---------------------------------------------------------
CS101 3.2 2007/08/11
CS022 3.4 2006/07/20
CS313 3.7 2005/11/11
CS101 3.3 2008/01/23
CS101 3.0 2009/02/10
CS022 3.3 2008/12/12
Now, I want the resulting table to pick and display the student records that are most recent,
which would look like this
SID[Varchar] GPA[Varchar] DATE_OF_UPDATE[Date]
---------------------------------------------------------
CS313 3.7 2005/11/11
CS101 3.0 2009/02/10
CS022 3.3 2008/12/12
Any Ideas ?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

are you familiar with using the GROUP BY statement? i think a SQL combined with some of the functions that are allowed using group by (MIN(),MAX()AVG())

would get you what you need....

What have you tried so far? This looks a little like homework, so I'd prefer to teach you how to fish, rather than feed you the answer.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mounish Old Hand Points: 322 More actions · Answer 2

Thanks for your reply..

I have tried the following, The only question is how can i manipulate it to display the GPA too

SELECT SID,MAX(DATE_OF_UPDATE) from S_TABLE

group by SID

rvphx SSCrazy Points: 2120 More actions · Answer 3

Try using the Ranking functions available. They are always a great help. I wouldnt provide the exact answer but here is a small snippet

with CTE(

your select from the base table,

RANK PARTITION BY (Primary_key/column_name) ORDER BY Update_Date DESC AS Rank1

)

SELECT * FROM CTE

WHERE Rank1 in (select MIN(Rank1))

something of that sort should help you. There are various other approaches to do this, just look around a bit.

Sanjay Rohra Ten Centuries Points: 1159 More actions · Answer 4

You can use Row_number function:

select a.sid, a.gpa, a.date_of_Update from

(select sid, gpa, date_of_Update, ROW_NUMBER() over (partition by sid order by date_of_update desc) as Rno

from table)a

where a.Rno = 1

Mounish Old Hand Points: 322 More actions · Answer 5

Thank y'all. I got the solution.

@ Rajiv: I've learnt a new concept from ur post, thanks

@ Rohra: Your approach is simple and precise. Thnx again mate :).