How to write this SQL Statement

Question

table1 has 3 columns:

Id UserName SubmittedDate
1 Joe 1/1/2006
2 Joe 1/1/2007
3 Nat 1/1/2008
4 Pat 1/1/2009

I want to return this:

Id UserName
2 Joe
3 Nat
4 Pat

I just want just one record for Joe, the most recent one.

How do I write this query? Thanks

Answer 1

SELECT  Id, UserName, SubmittedDate
FROM    (
        SELECT  Id, UserName, SubmittedDate,
                ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY SubmittedDate DESC) rn
        FROM    table1
        ) q
WHERE rn = 1

Answer 2

SELECT MAX(ID), UserName
FROM table
GROUP BY UserName

Note this assumes that higher ID means later. It doesn't query directly on the Submitted Date field. For that, use Quassnoi's much more complex one :)

Answer 3

With only standard SQL:

SELECT Id, UserName
FROM table1
JOIN (
    SELECT UserName, MAX(SubmittedDate) AS MaxDate
    FROM table1
    GROUP BY UserName
    ) AS MaxDateTable ON table1.UserName = MaxDateTable.UserName
WHERE SubmittedDate = MaxDate

Answer 4

Ripping off @Quassnoi, but I like evangelizing the use of CTEs in place of subqueries and most views.

WITH
query1 as(
    SELECT  Id, UserName, SubmittedDate,
            ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY SubmittedDate DESC) rn
    FROM    table1
        )

SELECT  Id, UserName, SubmittedDate
FROM   query1
WHERE rn = 1

Answer 5

Here are three potential solutions. Give them each a try and see which is better for your data and situation. Keep in mind that in situations where a username has two identical submitted_date values the results may not be what you're expecting.

SELECT
    T1.id,
    T1.username
FROM
    My_Table T1
INNER JOIN 
  (
   SELECT username, MAX(submitted_date) 
   FROM My_Table T2 GROUP BY username
  ) SQ 
    ON SQ.username = T1.username AND SQ.submitted_date = T1.submitted_date

.

SELECT
    T1.id,
    T1.username
FROM
    My_Table T1
WHERE
    NOT EXISTS 
    (
      SELECT * 
      FROM
         My_Table T2 
      WHERE
         T2.username = T1.username AND
         T2.submitted_date > T1.submitted_date
    )

.

SELECT
    T1.id,
    T1.username
FROM
    My_Table T1
LEFT OUTER JOIN My_Table T2 ON
    T2.username = T1.username AND
    T2.submitted_date > T1.submitted_date
WHERE
    T2.id IS NULL