0

I am trying to get the latest date among the column values mentioned but also fetching relative column values for the respective Max(date)

My Query:

select gd.desk_id, gd.created_by, max(gd.created_at) from dodem gd where gd.desk_id in ('abc123', 'xyz123') 
and gd.pot_id='123S2' group by gd.desk_id;

Now I have the following data column - desk_id, created_by, created_at, pot_id

abc123, sam, 2020-10-12 14:53:27, 123S2
xyz123, max, 2020-10-12 14:53:27, 123S2
xyz123, max, 2020-10-12 15:53:27, 123S2

With above query I should be getting two rows

abc123, sam, 2020-10-12 14:53:27, 123S2
xyz123, max, 2020-10-12 15:53:27, 123S2

which is absolutely working as it should be but the query is getting wrong results when I have similar data but different created_by then it does not fetches created_by of max(created_at) rather which comes first.

Example:

abc123, sam, 2020-10-12 14:53:27, 123S2
xyz123, max, 2020-10-12 14:53:27, 123S2
xyz123, jim, 2020-10-12 15:53:27, 123S2

Query Gives:

abc123, sam, 2020-10-12 14:53:27, 123S2
xyz123, max, 2020-10-12 15:53:27, 123S2

If you see I am getting max rather I should be getting jim for latest date corresponding column value.

Please suggest improvement in my query.

Kramer
  • 389
  • 8
  • 34
  • Probably learn to write a proper query to get the correct result. Please look up how to use `GROUP BY`. – Eric Oct 27 '20 at 18:16
  • @Eric if you could point out the correction that would be helpful rather criticizing. Like I said I wrote the same query please suggest where I am wrong – Kramer Oct 27 '20 at 18:27
  • @Kramer pls see the several different solutions in the duplicate question. – Shadow Oct 27 '20 at 18:56
  • @Kramer When you learn how to use proper `GROUP BY`, your problem will resolve by itself. Your query is not even valid. It won't even execute in most dbms system. Even MySQL 8 won't execute your code. – Eric Oct 27 '20 at 22:23
  • @Eric sorry my friend this query gives result although its wrong and I have resolved it with duplicate question – Kramer Oct 28 '20 at 08:58

0 Answers0