How can i select the distinct values from one col with their latest value from another

Question

I have 2 columns, names and datetime2. I want to select all distinct names with their latest value for mark according to a thrid col, Date.

eg table
name, mark, date
john, 20, 2014-05-17 01:00:01
sally, 30, 2014-05-18 05:00:00
john, 40, 204-05-17 02:00:00
sally, 50, 2014-05-18 04:30:00

result should be - 
john, 40, 2014-05-17 02:00:00
sally, 30, 2014-05-18 05:00:00

I tried

SELECT name, Mark
FROM table
GROUP BY name, date

and

select TOP 1 Name, Mark ,date  from table group by name, order by date desc

This is basic `group by` query. If you are using SQL, you should know the basics, and `group by` is basic. — Gordon Linoff, May 22 '14 at 16:17
Malk, i get error - date not contained within th eaggregate function — Fearghal, May 22 '14 at 16:21
@Fearghal Then you are doing something else wrong because that's the correct answer — Lamak, May 22 '14 at 16:24
Apologies, i just realised my question was missing a col - i need to get the name, latest 'mark' col value based on the date col — Fearghal, May 22 '14 at 16:25

score 1 · Accepted Answer · answered May 22 '14 at 16:29

1

You need a subSELECT to isolate the record with the latest date and use that to filter the primary query:

SELECT name, mark
FROM table t1
WHERE date=(
  SELECT MAX(date)
  FROM table t2
  WHERE t2.name=t1.name
)

answered May 22 '14 at 16:29

Frazz

2,995
2
19
33

Perfect - thanks a mil. I was wondering why the answers coming back were so simple, duh. – Fearghal May 22 '14 at 16:33

score 0 · Answer 2 · answered May 22 '14 at 16:21

There's a good site here on SQL basics which might be useful to you:

http://www.w3schools.com/sql/sql_groupby.asp

It gives you examples, plus you can test them out by typing your own SQL into the website, or modifying their examples to see the effects.

How can i select the distinct values from one col with their latest value from another

2 Answers2