CODEWITHSUNDEEP
mysqlsql
peris
peris

Reputation: 953

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

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

Upvotes: 0

Views: 74

Answers (2)

peris
peris

Reputation: 953

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. :)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

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
)

Upvotes: 1

Related Questions