Better solution for existing sql query

Question

I have table like this:

type       | date       | id
-----------------------------
1          | 2012-01-01 | 1
2          | 2012-01-01 | 2
1          | 2012-02-02 | 3
2          | 2012-02-02 | 4

I need to build query that will pick all "up-to-date" distinct values of type ( in this example it will be records with id's 3 and 4). Now i have this solution :

select * from test t1 where date = 
(select max(date) from test t2 where t2.type = t1.type ) order by type, date desc

I am embarrassed by the presence of nested select, maybe there is more elegant solution ?

Which RDBMS? This is perfect *(imho)* for MySQL for example. There are alternatives in other dialects. — MatBailie, Nov 28 '12 at 14:46
possible [duplicate](http://stackoverflow.com/q/612231/944681) — Michal Klouda, Nov 28 '12 at 14:46
Is what you are really asking is... what is the latest date activity for a given type -- thus resulting in returning records ID 3 & 4? — DRapp, Nov 28 '12 at 14:46
subselect aren't necessary a bad thing, you know? no need to be embarrassed ;) — fancyPants, Nov 28 '12 at 14:47

John Woo · Accepted Answer · 2012-11-28T14:51:42.267

3

since you didn't mention the RDBMS you are using, try this one. will work on most RDBMS.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  type, MAX(DATE) maxDAte
            FROM tableName
            GROUP BY type
        ) b ON a.type = b.type AND
                a.DATE = b.maxDate

SQLFiddle Demo

or if you RDBMS supports Window Function

SELECT type, date, id
FROM
(
    SELECT type, date, id,
            ROW_NUMBER() OVER (PARTITION BY type
                                ORDER BY date DESC) rn
    FROM tableNAme
) s
WHERE rn = 1

SQLFiddle Demo

edited Nov 28 '12 at 14:51

answered Nov 28 '12 at 14:46

John Woo

258,903
69
498
492

1

+1 : This is simply an Algebraic Re-arrangement of the same algorithm. It should be noted, however, that some RDBMS optimise this better than the OPs suggestion. *(And I've never known this to be **worse** than the OPs suggestion.)* *[I'd +1 the `ROW_NUMBER()` alternative too, but it won't let me ;)]* – MatBailie Nov 28 '12 at 14:49

score 1 · Answer 2 · answered Nov 28 '12 at 14:58

1

In your particular example, this could work also:

select type, max(date), max(id)
from your_table
group by type

but notice that it will work only if you are absolutely sure that dates and ids are always increasing. If this is not the case, max(date) and max(id) could be on two different rows. Use this only if you know what you are doing! If not, there's nothing wrong with nested queries.

answered Nov 28 '12 at 14:58

fthiella

48,073
15
90
106

Nice approach, i should think about this. – Chris Nov 28 '12 at 15:00

Better solution for existing sql query

2 Answers2