CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlgreatest-n-per-group
jojo
jojo

Reputation: 13843

How to select row with a largest value in its group?

I have a table, contains a list of tasks:

ID      TaskName       Date
----------------------------------
1       Task1          2010-11-23
2       Task2          2011-12-01
3       Task1          2012-02-01
4       Task3          2008-01-09
5       Task2          2007-03-09
6       Task3          2000-01-01

how to create a view contain task with its lastest perform date?

e.g result from the above table should be:

id  taskname  date
------------------------
3   Task1     2012-02-01
2   Task2     2011-12-01
4   Task3     2008-01-09

Upvotes: 1

Views: 226

Answers (2)

Brad Cathey
Brad Cathey

Reputation: 126

SELECT id, taskName, MAX(Date)
FROM table
GROUP BY taskName

(This will not work in Oracle because id is not in the GROUP BY)

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21766

SELECT
  ID,
  TaskName,
  Date
FROM
(
  select 
    ID,
    ROW_NUMBER() OVER (PARTITION BY TaskName ORDER BY Date DESC) Ordinal,
    TaskName,
    Date
  FROM YourTable
) t
WHERE Ordinal = 1

Upvotes: 2

Related Questions