Select one row with MAX(column) for known other several columns without subquery

Question

My table contains votes of users for different items. It has the following fields:

id, user_id, item_id, vote, utc_time

I understand how to get the last vote of #user# for #item#, but it uses subquery:

    SELECT votes.*, items.name, items.price
        FROM votes JOIN items ON items.id = votes.item_id
        WHERE user_id = #user# AND item_id = #item#
            AND utc_time = (
                SELECT MAX(utc_time) FROM votes
                WHERE user_id = #user# AND item_id = #item#
            )

It works, but it looks quite stupid to me... There should be a more elegant way to get this one record. I tried the approach suggested here, but I cannot make it work yet, so I'll appreciate your help: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

There is a second part to this question: Count rows with DISTINCT(several columns) and MAX(another column)

ypercubeᵀᴹ · Accepted Answer · 2013-01-20T23:34:20.343

2

You want just one row from the result, the one with MAX(utc_time). In MySQL, there is a LIMIT clause you can apply with ORDER BY:

SELECT votes.*, items.name, items.price
    FROM votes JOIN items ON items.id = votes.item_id
    WHERE user_id = #user# AND item_id = #item#
    ORDER BY votes.utc_time DESC
        LIMIT 1 ;

An index on either (user_id, item_id, utc_time) or (item_id, user_id, utc_time) will be good for efficiency.

edited Jan 20 '13 at 23:34

answered Jan 20 '13 at 22:57

ypercubeᵀᴹ

113,259
19
174
235

That's smart... It's not that I want one row, there are simply cannot be more than one with such conditions in my table... Isn't it going to be potentially "slow"? Or when MySQL sees LIMIT 1 it will just fetch the maximum without ordering the whole table? – esp Jan 20 '13 at 23:16
1

If it can use an index, it won't search (or order) the whole table (or the N rows that match the `WHERE` conditions). It will start from the (max) side of the index and fetch the first row only. – ypercubeᵀᴹ Jan 20 '13 at 23:19
I see... Yes, I actually have an index there already. Thanks! – esp Jan 20 '13 at 23:32
It works! Any idea what to do with this one (same table :): http://stackoverflow.com/questions/14430426/count-rows-with-distinctseveral-columns-and-maxanother-column/14430476#comment20089203_14430476 – esp Jan 20 '13 at 23:41

score 1 · Answer 2 · answered Jan 20 '13 at 23:08

1

Simple: if the date/time is the maximum date there will not exist a "higher" (more recent) date/time (for the same {user,item} ).

SELECT vo.*
     , it.name, it.price
  FROM votes vo
  JOIN items it ON it.id = vo.item_id
 WHERE vo.user_id = #user# AND vo.item_id = #item#
   AND NOT EXISTS (
       SELECT *
       FROM votes nx
       WHERE nx.user_id = vo.user_id
       AND nx.item_id = vo.item_id
       AND nx.utc_time > vo.utc_time
       );

answered Jan 20 '13 at 23:08

wildplasser

43,142
8
66
109

It still uses subquery... Isn't there a way to avoid it? – esp Jan 20 '13 at 23:13
No,of course not. Even window functions and CTE's will invoke subqueries (in a different form) Window functions come closest, but mysql does not have them. – wildplasser Jan 20 '13 at 23:15
1

BTW: a sane plan generator would yield the same plan as for the LIMIT subquery (given appropiate indexes). Extremely sane generators would even recognise the MAX() to generate the same plan. BTW2: if there are two records with date=max(date), both will be found (since there is no order. The LIMIT (or rownumber OVER window) would avoid ties. – wildplasser Jan 20 '13 at 23:46

Select one row with MAX(column) for known other several columns without subquery

2 Answers2

Linked