-1

Here's the query:

SELECT ID, Name, EventTime, State
FROM mytable as mm Where EventTime IN
(Select MAX(EventTime) from mytable mt where mt.id=mm.id)

Here is the fiddle: http://sqlfiddle.com/#!3/9630c0/5 It comes from this S.O. question: Select distinct rows whilst grouping by max value

I would like to hear in plain english how it works. I'm missing some fundamental understanding of part of it.

I don't really understand what the aliases are doing in the mt.id=mm.id part. It selects rows where the id is equal to the id?

Community
  • 1
  • 1
ADJenks
  • 2,973
  • 27
  • 38
  • you can select from same table several times in same query, read about self-joins and aliases. `mt` and `mm` allow you and machine to distinguish between recordsets that are used in the evaluation. – Bulat Aug 12 '15 at 08:30
  • Note that if you read the comments (and then there is another similar response), the `IN` is useless and you could use a `EventTime = (Select MAX` – xanatos Aug 12 '15 at 08:32

2 Answers2

1

The mt.id=mm.id part makes it a correlated subquery, hence the subquery is re-evaluated for each ID.

The query, then, selects the most recent event for each ID.

Jeremy
  • 5,055
  • 1
  • 28
  • 44
  • Thanks, I didn't know about correlated subqueries. So this statement is inneficient and the one that @Madhivanan wrote is likely to be more effecient on a larger table because it wont be executed once per row. Thank you for the terminology and for the reference. I'm sorry I cannot give you both the benefit of the correct answer, you were both helpful. – ADJenks Aug 12 '15 at 08:53
  • @adjenks - it *may* be inefficient but you shouldn't assume. For one, the performance difference may not be noticeable. For another, the query optimizer can do some pretty smart things. In SQL, you tell the system *what you want*, not *how to do it*, and then the optimizer tries to work out the best way to do that. – Damien_The_Unbeliever Aug 12 '15 at 10:18
1

It is basically translated into "Get me the data for each id with maximum EventTime associated with."

You can also rewrite the code as

SELECT t1.ID, t1.Name, t1.EventTime, t1.State FROM mytable as t1
inner join
(
select id,max(EventTime) as EventTime from mytable group by id
) as t2 on t1.id=t2.id and t1.EventTime=t2.EventTime
Madhivanan
  • 13,470
  • 1
  • 24
  • 29