CODEWITHSUNDEEP
sqlsql-serverjoingroup-by
lingo
lingo

Reputation: 1908

Group by with getting only max value

I have three tables

I want to execute a query, which joins the data of max event id for every machine, but I have some problems with grouping.

Table machine:

machine
-----------
Machine 1
Machine 2

Table event:

eventid | machine   | start      | stop
-----------------------------------------------
      1 | Machine 1 | 2016-06-14 | 2016-06-16
      2 | Machine 1 | 2016-06-16 | null
      1 | Machine 2 | 2016-05-30 | null

Table eventdetails:

eventid | machine   | name       | value
-----------------------------------------------
      1 | Machine 1 | product    | Product 1
      1 | Machine 1 | person     | Samuel

I have tried following query, but it returns more than one row per machine... I want to group by machine and get only data according to max event id.

SELECT m.machine, MAX(e.eventid), e.start, e.stop, d.name, d.value
FROM machine m 
JOIN event e ON e.machine = m.machine
JOIN eventdetails d ON d.eventid = e.eventid AND d.machine = m.machine
GROUP BY m.machine, e.start, e.stop, d.name, d.value

Thanks in advance.

Upvotes: 1

Views: 56

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

Give this one a go. It gives you an inner join that will suppress anything from the event table what doesn't equal the max(eventid) from the same table.

SELECT m.machine, e.eventid, e.start, e.stop, d.name, d.value
FROM machine m 
INNER JOIN event e ON e.machine = m.machine
INNER JOIN eventdetails d ON d.eventid = e.eventid AND d.machine = m.machine
INNER JOIN (SELECT machine, MAX(eventid) maxevent FROM event GROUP BY machine) ma ON m.machine = ma.machine AND e.eventid = ma.maxevent
GROUP BY m.machine, e.start, e.stop, d.name, d.value

Upvotes: 2

Related Questions