CODEWITHSUNDEEP
mysqlsqlselectdistinct
Ben
Ben

Reputation: 4279

SQL select distinct only highest values

Lets say we have such table:

table

i only what to select unique plans with highest time, and also being able to select other columns. To ilustrate here's a query(witch obviously wouldn't work):

SELECT DISTINCT(plan), time, id
FROM table
ORDER BY time desc

how can i get such table:

plan|time|id
----+----+--------
1   |0   |9
2   |90  |10
3   |180 |11
4   |360 |12
5   |720 |13
6   |1080|15
7   |0   |16
8   |720 |23

Upvotes: 1

Views: 6411

Answers (3)

Lijo
Lijo

Reputation: 6778

SELECT plan, max(time), id FROM demo group by plan

check fiddle

Upvotes: -4

Kuba Wyrostek
Kuba Wyrostek

Reputation: 6221

If highest time is unique per plan, and you need to select more columns, then a self join should do

select mytable.* from
mytable
inner join
  (
  select plan, max(time) as maxtime
  from mytable
  group by plan
  ) as maxtimes
  on mytable.plan = maxtimes.plan and mytable.time = maxtimes.maxtime

Upvotes: 3

Mureinik
Mureinik

Reputation: 311018

This sounds like a simple aggregate query:

SELECT   plan, MAX(time)
FROM     mytable
GROUP BY plan

Upvotes: 4

Related Questions