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

Question

Post reply

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: 323482 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

Mounish

Old Hand

Points: 322

January 6, 2011 at 1:57 pm

#1270321

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.