Rewrite the MySQL query without using UNION

advertisements

This question already has an answer here:

  • Get top n records for each group of grouped results 8 answers
  • Get records with highest/smallest <whatever> per group 1 answer

I have the following query which returns the correct results but I'm sure it's not the best way to get these results...

select * from (
    select * from features where feature_area = 0
    order by updateStamp desc limit 1
) as feature_1

union all

select * from (
    select * from features where feature_area = 1
    order by updateStamp desc limit 1
) as feature_2

union all

select * from (
    select * from features where feature_area = 2
    order by updateStamp desc limit 1
) as feature_3

This returns results which look something like...

id    feature_area        title                  updateStamp
--------------------------------------------------------------------
103   0                   This is a title        2014-04-15 09:26:14
102   1                   Another title          2014-03-27 14:09:49
98    2                   More title             2014-01-21 16:00:55

Could this be improved using joins rather than unions and if so could you point me in the right direction please.

EDIT:

Having looked at the other options pointed out by @Ben it would seem I've already got the quickest query (albeit not that attractive) for my particular purpose. Feel free to correct me if you think I'm wrong though. I'm no expert, hence I'm asking for advice.


select f.* from features f
inner join (
    select
    feature_area
    max(updateStamp) as updateStamp
    from
    features
    where feature_are IN (0,1,2)
    group by feature_area
) sq  on sq.feature_area = f.feature_area
and sq.updateStamp = f.updateStamp