Query to SELECT non-repeating values from table

Question

I have a table structured as below:

ID        Name             RunDate
10001     Item 1           12/09/2013 02:11:47
10002     Item 2           12/09/2013 01:13:25
10001     Item 1           12/09/2013 01:11:37
10007     Item 7           12/08/2013 11:02:04
10001     Item 1           12/08/2013 10:25:00

My problem is that this table will be sent to a distribution group email and it makes the e-mail so big because the table has more than hundreds of rows. What I want to achieve is to only show the records that have DISTINCT ID showing only the most-recent RunDate.

ID        Name             RunDate
10001     Item 1           12/09/2013 02:11:47
10002     Item 2           12/09/2013 01:13:25
10007     Item 7           12/08/2013 11:02:04

Any idea how I can do this? I'm not very good with aggregate stuff and I've used DISTINCT but it always mess up my query.

Thanks!

Answer 1

This is more flexible because it doesn't require grouping by all columns:

;WITH x AS
(
  SELECT ID, Name, RunDate, /* other columns, */
    rn = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RunDate DESC)
  FROM dbo.TableName
)
SELECT ID, Name, RunDate /* , other columns */
  FROM x
  WHERE rn = 1
  ORDER BY ID;

(Since Name doesn't really need to be grouped, and in fact shouldn't even be in this table, and the next follow-up question to the GROUP BY solution is almost always, "How do I add <column x> and <column y> to the output, if they have different values and can't be added to the GROUP BY?")

Answer 2

Group by the values that should be distinct and use max() to get the most current date

select id, name, max(rundate) as rundate
from your_table
group by id, name