1

I have below mentioned table:

ID      Date                    Value
AL-1    2017-04-01 22:04:08     154
AL-1    2017-04-05 15:08:45     158
AL-1    2017-04-06 18:09:15     225
AL-1    2017-04-08 20:08:17     254
AL-2    2017-04-01 22:04:08     154

I am trying this: select ID, Value from table1 where id in ('AL-1','AL-2') and group by max(date(Date)); but it gives me output like:

ID      Date                    Value
AL-1    2017-04-01 22:04:08     154
AL-2    2017-04-01 22:04:08     154

Desired Output:

ID      Date                    Value
AL-1    2017-04-08 20:08:17     254
AL-2    2017-04-01 22:04:08     154
Roy1245
  • 507
  • 4
  • 18
  • 2
    `ORDER BY \`Date\` DESC LIMIT 1`? – MatBailie May 09 '18 at 12:25
  • What's the expected result if there are two rows with the same latest timestamp? – jarlh May 09 '18 at 12:26
  • 1
    Possible duplicate of [Select last row in MySQL](https://stackoverflow.com/questions/4073923/select-last-row-in-mysql) – Nick May 09 '18 at 12:34
  • FWIW, I'm increasingly of the opinion that these sorts of 'intelligent' ids that have some meaning beyond the scope of the database just cause trouble further down the road. – Strawberry May 09 '18 at 13:09

6 Answers6

1

You can use correlated subquery if you want to fetch latest row for each ID:

select t1.*
from table1 t1
where Date = (select max(t2.Date) from tabel1 t2 where t1.ID = t2.ID);
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • Or join on a non-correlated sub-query : `SELECT id, MAX(date)` *(which I believe MySQL performs better with)* – MatBailie May 09 '18 at 12:39
1

Have a sub-query that returns each id with its max date. JOIN with that result:

select t1.ID, t1.Value, t1.date
from table1 t1
join (select id, max(Date) maxdate
      from table1
      where id in ('AL-1','AL-2')
      group by id) t2 on t1.ID = t2.ID and date(t1.Date) = date(t2.maxdate)
where t1.id in ('AL-1','AL-2')
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • External `WHERE` is redundant as it is also resolved by pushing the join predicate down in to the sub-query's `WHERE` clause. *(Or vice versa, I'd probably keep just the external `WHERE` clause and remove the internal `WHERE` clause.)* – MatBailie May 09 '18 at 12:40
  • @MatBailie, I kept it because I don't know MySQL optimization very well. Will it make any performance difference? – jarlh May 09 '18 at 12:43
  • None, if you removed the inner `WHERE` then it would be like joining on a view; predicate-push-down, macro-like-expansion, whatever. If MySQL can't handle this then it can't handle views ;) – MatBailie May 09 '18 at 12:44
  • @jarlh It's taking too much time to run the query. – Roy1245 May 09 '18 at 12:48
  • Add table and index definitions to the question. And the execution plan. – jarlh May 09 '18 at 12:56
0

Use order by desc with limit

select ID, Value from table1 order by date desc limit 1
Devsi Odedra
  • 5,244
  • 1
  • 23
  • 37
0

Try like below:

SELECT * FROM table t
WHERE t.date =(SELECT MAX(date) FROM table )
Pelin
  • 936
  • 5
  • 12
0

Try this:

select ID, Value 
from table1 
where id='AL-1' 
group by date desc 
limit 1
Rahul Jain
  • 1,319
  • 7
  • 16
0
SELECT ID, SUBSTRING_INDEX(GROUP_CONCAT(DATE ORDER BY DATE DESC SEPARATOR '##'),'##',-1) AS DATE, VALUE
FROM table1 GROUP BY ID
Hosain Ahmed
  • 115
  • 6
  • While this answer is probably correct and useful, it is preferred if you include some explanation along with it to explain how it helps to solve the problem. This becomes especially useful in the future, if there is a change (possibly unrelated) that causes it to stop working and users need to understand how it once worked. – Erty Seidohl May 09 '18 at 14:40