Select first row from ordered group without SUBSTRING_INDEX(GROUP_CONCAT())

Question

Is there other way in MySQL to achieve the same effect as query below without GROUP_CONCAT and SUBSTRING_INDEX trick?

How bad is this query when I have thousands of tickets per performance?

The point is to group tickets by performance, in these groups order tickets by status and select first ticket.id per group.

SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY status), ',', 1)
FROM ticket
GROUP BY performance_id

In other engines I would use something like this:

SELECT FIRST(t.id)
FROM (SELECT * FROM ticket ORDER BY status) AS t
GROUP BY performance_id

But MySQL doesn't have FIRST or related operator.

You basically want the first item in a group – Shadow Jun 27 '18 at 10:43 — Shadow, Jun 27 '18 at 10:43

score 0 · Answer 1 · answered Jun 27 '18 at 10:41

Try this:

If you want the first id (in ascending mode)

So if you have ticket 1, 2, 3 with the same group, you'll have only ticket 1

SELECT t1.id
FROM ticket t1
WHERE NOT EXISTS
    (SELECT 'PREVIOUS'
    FROM ticket t2
    WHERE t2.id < t1.id
    AND t2.performance_id = t1.performance_id)

If you want the first id (in descending mode)

So if you have ticket 1, 2, 3 with the same group, you'll have only ticket 3

SELECT t1.id
FROM ticket t1
WHERE NOT EXISTS
    (SELECT 'NEXT'
    FROM ticket t2
    WHERE t2.id > t1.id
    AND t2.performance_id = t1.performance_id)

These queries sort by `id`, rather than by `status`, which is what OP asks for. In other words, they want one `id` from each group, with the `id` selected as the first one when sorting rows by `status`. Would it still work to use `t2.status > t1.status`, if the value of `status` can be compared using `>`? — Quinn Comendant, May 08 '20 at 01:15

Select first row from ordered group without SUBSTRING_INDEX(GROUP_CONCAT())

1 Answers1