CODEWITHSUNDEEP
sql
Ray
Ray

Reputation: 133

How to perform SQL Query to get last entry

I am working on creating a SQL query where the result will return a student test score from the last test that they took. I think that this should be fairly simple but I am just not seeing it.

Here is my test data

Name    Date        Score
John    2/3/2012    94
John    2/14/2012   82
John    2/28/2012   72
Mary    2/3/2012    80
Mary    2/28/2012   71
Ken     2/14/2012   68
Ken     2/14/2012   66

I want the returned result to be

John    2/28/2012   72
Mary    2/28/2012   80
Ken     2/14/2012   66

I appreciate any assistance.

Upvotes: 1

Views: 167

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Which database are you using? Most support row_number() which is the right way to answer this:

select *
from 
(
  select t.*, row_number() over (partition by name order by date desc) as seqnum
  from table t
)
where rownum = 1

Upvotes: 0

Taryn
Taryn

Reputation: 247810

select date, name, score
from temp t1
where date = (select max(date) from temp where t1.name = temp.name)

OR

SELECT a.*
FROM temp a
INNER JOIN 
(
  SELECT name,MAX(date) as max_date 
  FROM temp a
  GROUP BY name
)b ON (b.name = a.name AND a.date=b.max_date)

Here is a sql fiddle with an example

or even this if you have more than one record for each person on a date like you show in your sample data.

SELECT c.name,c.date, MAX(c.score) as max_score
FROM
(
 SELECT a.* 
 FROM temp a
 INNER JOIN 
 (
  SELECT name,MAX(date) as max_date 
  FROM temp a
  GROUP BY name
 )b ON (b.name = a.name AND a.date=b.max_date)
)c
group by c.name,c.date

Sql fiddle with this example

Upvotes: 3

Sam DeHaan
Sam DeHaan

Reputation: 10325

SELECT Name, Date, Score
FROM tablename t1
WHERE Date = (SELECT MAX(Date) 
              FROM tablename
              WHERE Name = t1.Name
              GROUP BY Name)

Upvotes: 1

Related Questions