Select one row for each different value of column, MySQL

Question

Hi Guys,

Im a bit stuck and cant get my head around this MySQL. Here are my abridged tables I wish to query:

print(printID, eventID, printTime)

sales(saleID, eventID, saleTime)

I wish to get the last print time for each event, then select the sale IDs which have that eventID with a sale time greater than the last print time.

I've tried loads of ways but I can't figure it. Please help!

Answer 1

To get the last print time:

SELECT MAX(p.printTime), p.eventID FROM print p

You can use this in a subquery to get what you need:

    SELECT s.saleID, s.eventID 
      FROM sales s
INNER JOIN (SELECT MAX(p.printTime) AS lastPrintTime, p.eventID 
              FROM print p
          GROUP BY p.eventID) print_time
WHERE print_time.eventID = s.eventID 
  AND s.saleTime > print_time.lastPrintTime

Does that help?

Answer 2

select s.saleID, s.eventID, s.saleTime, lp.LastPrintTime
from (
    select eventID, max(printTime) as LastPrintTime
    from print
    group by eventID
) lp
inner join sales s on lp.eventID = s.eventID 
    and saleTime > lp.LastPrintTime

Answer 3

Inner Query:

SELECT * FROM sales AS s INNER JOIN
(
    SELECT eventID, max(printTime) as maxPrintTime FROM [print] GROUP BY eID 
) as p ON p.eventID = s.eventIDAND s.saleTime > maxPrintTime