How do I group by the MAX() in SQL?

Question

Table:

+------------+------------------+
|     Number |       Date       |
+------------+------------------+
|          1 | 1900-01-01       |
|          1 | 2012-01-01       |
|          1 | 2011-01-01       |
|          2 | 1900-01-01       |
|          2 | 2007-01-01       |
+------------+------------------+

The result needs to be:

+------------+------------+
|     Number |    Date    |
+------------+------------+
|          1 | 2012-01-01 |
|          2 | 2007-01-01 |
+------------+------------+

and so on...

EDIT -

Table:

+------------+------------------+------------+
|     Number |       Date       |   Field3   |
+------------+------------------+------------+
|          1 | 1900-01-01       |     a      |
|          1 | 2012-01-01       |     b      |
|          1 | 2011-01-01       |     c      |
|          2 | 1900-01-01       |     a      |
|          2 | 2007-01-01       |     b      |
+------------+------------------+------------+

I want to SELECT the DISTINCT of Number WHERE it's GROUP's Date = MAX.

So after the query I would get:

1...b

2...b

Check out this answer http://stackoverflow.com/questions/1305056/selecting-all-corresponding-fields-using-max-and-group-by — classicjonesynz, Mar 05 '13 at 23:12

Taryn · Accepted Answer · 2013-03-05T23:27:08.850

4

~~You will need to use an aggregate function max() with a GROUP BY:~~

SELECT number, max(date)
from yourtable
group by number

See SQL Fiddle with Demo

Depending on your database you can use row_number():

select number, date
from
(
  select number, date,
    row_number() over(partition by number order by date desc) rn
  from yourtable
) src
where rn = 1

See SQL Fiddle with Demo.

This can also be written using a subquery:

select t1.*
from yourtable t1
inner join
(
  SELECT number, max(date) MaxDate
  from yourtable
  group by number
) t2
  on t1.number = t2.number
  and t1.date = t2.maxdate;

See SQL Fiddle with Demo

edited Mar 05 '13 at 23:27

answered Mar 05 '13 at 23:06

Taryn

242,637
56
362
405

what if I don't need to capture the Date? what if I need to run a select on all numbers WHERE MAX(date) for EACH? – JJ. Mar 05 '13 at 23:11
@Rj. I don't understand what you are asking, can you clarify? Both of these queries produce the result that you requested. – Taryn Mar 05 '13 at 23:12
I want to do the same thing but I do NOT want to select the Date. I want to get all records where MAX(date). Let me try to rephrase: For each Number, I want to group it and then do a WHERE this GROUP has a MAX(date). – JJ. Mar 05 '13 at 23:15
@Rj. can you edit your original post with more details, data? I am missing something. Is your desired result not correct? – Taryn Mar 05 '13 at 23:18
@Rj. See my edit, both the second and third version will still work. – Taryn Mar 05 '13 at 23:27

How do I group by the MAX() in SQL?

1 Answers1