SQL: retrieve columns that are not in a aggregate or group statement

Question

I have the following data.

--------------------------------------------------------------------------------
id             | value_name    | last_modified | system_id     | value_data    |
--------------------------------------------------------------------------------
1              | name1         | 2012-06-04    | 1             | aaa           |
2              | name1         | 2012-02-04    | 1             | bbb           |
3              | name1         | 2012-04-25    | 1             | ccc           |
4              | name2         | 2012-07-04    | 1             | ddd           |
5              | name2         | 2012-06-14    | 1             | eee           |
6              | name3         | 2011-09-05    | 1             | qqq           |
--------------------------------------------------------------------------------

Now I need to get only most recently modified values.

update

For the table above I want to get the following result

--------------------------------------------------------------------------------
id             | value_name    | last_modified | system_id     | value_data    |
--------------------------------------------------------------------------------
1              | name1         | 2012-06-04    | 1             | aaa           |
4              | name2         | 2012-07-04    | 1             | ddd           |
6              | name3         | 2011-09-05    | 1             | qqq           |
--------------------------------------------------------------------------------

I searched a lot here and in google as well, but the solutions I was able to find doesn't actually work. For example one of the proposed solutions is usign join.

SELECT
    v.[id],
    v.[system_id],
    v.[value_name],
    v.[value_data]
FROM
    [values] v INNER JOIN
    (
        SELECT
            v.[id],      
            MAX(v.[last_modified]) AS last_modified
        FROM
            [values] v
        WHERE
            v.[system_id] = 1
        GROUP BY
            v.[id]
    ) s 
ON
    v.[id] = s.[id]

But sub-query is grouped by id (which is unique), so it will not calculate max last modification dates. I'm using SQLite and simple query like that works for me

SELECT
    v.[id],
    v.[system_id],
    v.[value_name],
    v.[value_data],
    MAX(v.[last_modified]) AS last_modified
FROM
    [values] v
WHERE
    v.[system_id] = 1
GROUP BY
    v.[value_name]

But I remember from Oracle that you can't select fields that are not listed in either aggregate functions or in group by statement, so I'm not sure this is guaranteed to give me expected result. Any suggestions how to solve that?

Thanks in advance.

I mean values with MAX last modification date. Updated the question. — axe, Jul 18 '12 at 07:55
Shouldn't you use `value_name` instead of ID? Id is unique as it is... — Nikola Markovinović, Jul 18 '12 at 07:56
Didn't get your question. What do you mean? Where should I use value_name instead of id? In group by? — axe, Jul 18 '12 at 07:57
I don't understand your question yet.How many last modified value you want? — SMK, Jul 18 '12 at 07:57
I want one value per each value_name with MAX(last_modified) date. — axe, Jul 18 '12 at 07:58
Everywhere. You need to group by value_name, select that value_name and join back to original table by value_name and date. That is, if date is unique. — Nikola Markovinović, Jul 18 '12 at 08:00
why you dont use groupby function with value_name with selection of Max(last_modified) date? — SMK, Jul 18 '12 at 08:00
@Shoaib Muhammad Khan: Because besides value_name I need to select system_id, value data and many other non listed in the example fields. Adding value_data to the group by statement will not give me the correct result. — axe, Jul 18 '12 at 08:03

score 1 · Accepted Answer · answered Jul 18 '12 at 08:07

1

If last_modified is unique per value_name, you can use this:

SELECT
    v.[id],
    v.[system_id],
    v.[value_name]
FROM
    [values] v INNER JOIN
    (
        SELECT
            v.[value_name],      
            MAX(v.[last_modified]) AS last_modified
        FROM
            [values] v
        WHERE
            v.[system_id] = 1
        GROUP BY
            v.[value_name]
    ) s 
ON
    v.[value_name] = s.[value_name]
AND
    v.[last_modified] = s.[last_modified]

If not, you will need to extract last id for last_modified grouped by value_name:

SELECT
    v.[id],
    v.[system_id],
    v.[value_name]
FROM
    [values] v INNER JOIN
    (
        SELECT max(id) ID
          FROM [values] v_max
         INNER JOIN
         (
             SELECT
                 [value_name],      
                 MAX([last_modified]) AS last_modified
             FROM
                 [values]
             WHERE
                 [system_id] = 1
             GROUP BY
                 [value_name]
         ) s 
          ON
              v_max.[value_name] = s.[value_name]
          AND
              v_max.[last_modified] = s.[last_modified]
         GROUP BY s.value_name, s.last_modified
   ) maxID
   ON v.ID = maxID.ID

Disclaimer: not tested.

answered Jul 18 '12 at 08:07

Nikola Markovinović

18,963
5
46
51

@axe: I've re-edited my answer. If you were testing it, please try again. – Nikola Markovinović Jul 18 '12 at 08:12
Not in sqlite as far as I know. Databases that implement Window Functions can greatly simplify this kind of queries. – Nikola Markovinović Jul 18 '12 at 08:18
last_modified is not unique. The second query will probably work, but you need to add WHERE [system_id] = 1 filtering condition to the SELECT max(id) subquery as well. The problem is in real life it's not that simple, there are about 5 filters like that and last modification dates are stored in other linked table. If I take this approach this select statement will be really huge. Isn't there any kind of shorter solutions for this? – axe Jul 18 '12 at 08:20
I can't even create view here in order to reduce query size.. :( – axe Jul 18 '12 at 08:24
Yes. Got it before you answered and deleted my comment. Thanks. – axe Jul 18 '12 at 08:25
http://stackoverflow.com/questions/6182546/how-do-i-group-and-order-with-sql-at-the-same-time In this topic it's said that SQLite supports the syntax I described at the end of my question, but I can't find prove. Do you have any information on that? – axe Jul 18 '12 at 08:59
No. Sqlite allows this syntax judging by Ypercubes answer BUT you will get additional columns from a [random record](http://www.sqlite.org/lang_select.html#resultset) as it is hinted in the answer. – Nikola Markovinović Jul 18 '12 at 09:09
Thanks. I wrote it with sub queries. It looks awfull, but I can't find anything better. Thank you. – axe Jul 18 '12 at 10:34
@axe You are welcome. I'm sorry I could not provide simpler answer. – Nikola Markovinović Jul 18 '12 at 10:35
I guess simple answer just doesn't exist :) – axe Jul 18 '12 at 12:27

SQL: retrieve columns that are not in a aggregate or group statement

1 Answers1