Moishey Schwartz Moishey Schwartz - 4 months ago 8
SQL Question

MySQL group multiple rows based on DISTINCT value

I need to display the last 2 results from a table (results), the results are comprised of several rows with matching submissionId, The number of rows per submission is unknown, and of course I prefer a single query.

Here is the DB table structure

submissionId input value

1 name jay
1 phone 123-4567
1 email test@gmail.com
2 name mo
2 age 32
3 name abe
3 email abe@gmail.com
4 name jack
4 phone 123-4567
4 email jack@gmail.com


Desierd results:

submissionId input value

3 name abe
3 email abe@gmail.com
4 name jack
4 phone 123-4567
4 email jack@gmail.com


Or even better, if I can combine the rows like this:

3 name abe 3 email abe@gmail.com
4 name jack 4 phone 123-4567 4 email jack@gmail.com

Answer

One option here is to use a subquery to identify the most recent and next to most recent submissionId:

SELECT submissionId, input, value
FROM yourTable
WHERE submissionId >= (SELECT MAX(submissionId) FROM yourTable) - 1
ORDER BY submissionId

Demo here:

SQLFiddle

Update:

If your submissionId column were really a date type, and you wanted the most recent two dates in your result set, then the following query will achieve that. Note that the subquery in the WHERE clause, while ugly, is not correlated to the outer query. This means that the MySQL optimizer should be able to figure out that it only needs to run it once.

SELECT submissionDate, input, value
FROM yourTable
WHERE submissionDate >=
    (SELECT MAX(CASE WHEN submissionDate = (SELECT MAX(submissionDate) FROM yourTable)
                    THEN '1000-01-01'
                    ELSE submissionDate
               END) FROM yourTable)
ORDER BY submissionDate

SQLFiddle

Comments