Geek Answers Handbook

How to choose the first row for each group (sorted by date)?

How to select the first row for each group in MySQL (sorted by date)?

I tried and I got to this:

SELECT * FROM `cats` WHERE `war_id` = 0 AND `eyes_color` = 'green' AND `id` IN (SELECT `id` FROM (SELECT * FROM `cats` ORDER BY `date_last_fought` DESC) AS t GROUP BY `war_id`, `n_of_medals`)

It works, but I'm not sure if it is the best.

Do you think I can simplify this?

+4
source share
2 answers

I'm trying to simplify this ...

 SELECT * FROM `cats` inner join (select * from cats WHERE `warid` = 1 AND `color` = 'green' order by orderDate desc) a on (cats.id=a.id) group by cats.warId;


you can see here http://sqlfiddle.com/#!2/55f5b/5
but I'm not sure if this is better than your request, ... reading and acting from EXPLAIN still becomes my homework: D

 ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA 1 PRIMARY <derived2> ALL (null) (null) (null) (null) 2 Using temporary; Using filesort 1 PRIMARY cats eq_ref PRIMARY PRIMARY 4 a.id 1 2 DERIVED cats ALL (null) (null) (null) (null) 6 Using filesort

and adding an index will speed it up

+5
source

Here is my solution:

 SELECT * FROM `cats` c JOIN ( SELECT `war_id`, `n_of_medals`, MAX(`date_last_fought`) date_last_fought FROM `cats` WHERE `war_id` = 0 AND `eyes_color` = 'green' GROUP BY `war_id`, `n_of_medals` ) c_summary ON c_summary.war_id = c.war_id AND c_summary.n_of_medals = c.n_of_medals AND c_summary.date_last_fought = c.date_last_fought ORDER BY c.`date_last_fought` DESC;

http://sqlfiddle.com/#!2/05bcb/2

0
source

More articles:


All Articles