Fetch only 2 results, one for each different value of a concrete field

Question

In a MYSQL table with those 5 fields: id, user_id, date, type, uid where type can be 1 or 2, I'm looking for a single query where I can fetch 2 results, one for type=1 and another one for type=2 based on date field.

Right now i have the following query which only gives me the last uid without taking care of the type field.

    SELECT t.uid
    FROM table AS t
    WHERE t.user_id = 666
    ORDER BY t.date
    DESC LIMIT 1

Does anyone know how should modify this query so i can get the last uid for type=1 and the last one for type=2 based on date field? I would like to keep a a single query

You could either use `user-defined variables` to create a row number per `type` or probably the easiest solution would be to use `union`... — sgeddes, Aug 11 '16 at 18:56
Try this https://stackoverflow.com/questions/32056885/select-the-last-two-records-in-sql-server-when-there-is-no-identity-column — Nick B, Aug 11 '16 at 18:57

Gordon Linoff · Answer 1 · 2016-08-11T21:44:38.003

1

Union all is probably the simplest method:

(select t.*
 from t
 where t.user_id = 666 and t.type = 1
 order by date desc
 limit 1
) union all
(select t.*
 from t
 where t.user_id = 666 and t.type = 2
 order by date desc
 limit 1
)

edited Aug 11 '16 at 21:44

answered Aug 11 '16 at 18:57

Gordon Linoff

1,242,037
58
646
786

Thanks for your answer :) I updated the post, i need to fetch 2 results one for each type value, one for type=1 and another one for type=2. The union all was something i already tried but it didn't look too clean to me. I was wondering for something cleaner and good performance. – peris Aug 11 '16 at 19:19
For your purposes, the `union all` is probably best. An index on `user_id, type, date)` would help performance. – Gordon Linoff Aug 11 '16 at 21:45
I've just posted a cleaner solution, just in case it interests you or anyone else. I've always felt like most people uses UNION to achieve results they don't properly know how to do it so i'm not an UNION enthusiasts. Anyway, thanks a lot for your time :) – peris Aug 12 '16 at 18:39

score 0 · Accepted Answer · answered Aug 12 '16 at 11:20

Finally i updated the query following this "paradigm":

http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html http://jan.kneschke.de/projects/mysql/groupwise-max/

This is how the query ended up:

SELECT s1.type, s1.uid  
FROM t AS s1  
LEFT JOIN t AS s2 ON s1.type = s2.type AND s1.date < s2.date  
WHERE s2.date IS NULL;

Here's a visual example: http://hastebin.com/ibinidasuw.vhdl

Credits are for snoyes from #sql on Freenode. :)

Fetch only 2 results, one for each different value of a concrete field

2 Answers2