GeneralTomfoolery GeneralTomfoolery - 1 month ago 5
MySQL Question

Returning multiple records whist using a MAX function?

I'm not sure how to pose this question... This SQL string returns 1 result (I think due to the MAX part) but I would like it to return all records.

So is there a way in one SQL statement to retrieve all the records related to the event (WHERE ticket_event_no = :id) AND find the MAX finish date of the recordset (find the latest ticket_finish dateof all tickets found)?

SELECT
MAX( ticket_finish ) AS cutOff, ticket_price, ticket_id, ticket_name, ticket_qty, ticket_start,
ticket_finish, ticket_max, ticket_type_no, ticket_min, ticket_order, ticket_fee
FROM
ticket
WHERE
ticket_event_no = :id
AND
ticket_hide = 0


Thanks in advance.

Answer

You just need to grab your max date with a subquery in your select list:

SELECT
    (SELECT Max(ticket_finish) from ticket) as cutOff, 
    ticket_price, 
    ticket_id, 
    ticket_name, 
    ticket_qty, 
    ticket_start, 
    ticket_finish, 
    ticket_max, 
    ticket_type_no, 
    ticket_min,
    ticket_order, 
    ticket_fee
FROM ticket
WHERE 
    ticket_event_no = :id
    AND ticket_hide = 0

That will pull the max value of ticket_finish and populate it as cutOff in all rows.

Comments