Organizational Research By

Surprising Reserch Topic

oracle sql group by not a group by expression help


oracle sql group by not a group by expression help  using -'sql,oracle,group-by'

I have a table some_table like

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   |
| 2      | 2        | 2011-04-20 21:03:04 | 229   |
| 3      | 3        | 2011-04-20 21:03:03 | 130   |
| 4      | 1        | 2011-04-20 21:02:09 | 97    |
| 5      | 2        | 2011-04-20 21:02:08 | 65    |
| 6      | 3        | 2011-04-20 21:02:07 | 101   |
| ...    | ...      | ...                 | ...   |
+--------+----------+---------------------+-------+


And I want the latest records for the other_id 1, 2, and 3. The obvious query I came up with is

SELECT id, other_id, MAX(date_value), value
  FROM some_table
 WHERE other_id IN (1, 2, 3)
 GROUP BY other_id


However it spits a "not a GROUP BY expression" exception. I tried adding all other fields (i.e. id, value) in the GROUP BY clause, but that just returns everything, exactly as if there was no GROUP BY clause. (Well, it does make sense too.)

So... I'm reading the Oracle SQL manual, and all I can find are some examples involving only queries with two or three columns and some i-have-never-seen-before grouping functions. How do I go and return

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   |
| 2      | 2        | 2011-04-20 21:03:04 | 229   |
| 3      | 3        | 2011-04-20 21:03:03 | 130   |
+--------+----------+---------------------+-------+


(the latest entries for each other_id) ? Thank you.
    

asked Oct 13, 2015 by virendra.bajaj
0 votes
3 views



Related Hot Questions

3 Answers

0 votes
 select id, other_id, date_value, value from
 (
   SELECT id, other_id, date_value, value, 
   ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
   FROM some_table 
   WHERE other_id IN (1, 2, 3) 
 )
 where r = 1
answered Oct 13, 2015 by sumit_jaiswalmca
0 votes

You cannot SELECT any column that is not either an aggregate or computed from only the columns used in the GROUP BY clause.

However there are three ways to do it:

  • You can use analytic functions

    SELECT id, other_id, date_value, value
      FROM ( SELECT id, other_id, date_value, MAX(date_value) OVER (partition by other_id) max_date, value
               FROM some_table )
     WHERE max_date = date_value;
    
  • You can use a self join with a ‚Äúgreater than ‚ÄĚ clause and detect your max this way

    SELECT t1.id, t1.other_id, t1.date_value, t1.value
      FROM some_table t1
      LEFT OUTER JOIN some_table t2
                   ON ( t1.other_id = t2.other_id AND t2.date_value > t1.date_value )
     WHERE t2.other_id IS NULL
    
  • You can use a subquery

      WITH max AS ( SELECT other_id, MAX(date_value) FROM some_table GROUP BY other_id )
    SELECT id, other_id, date_value, value
      FROM some_table
     WHERE ( other_id, date_value ) IN ( SELECT * FROM max )
    
answered Oct 13, 2015 by maurya
0 votes

Probably this is the simplest way

SELECT id, other_id, date_value, value
FROM some_table
WHERE date_value in (SELECT MAX(date_value)
                     from some_table
                     GROUP BY other_id
                     HAVING other_id in (1,2,3));

Test the above query here

answered Oct 13, 2015 by thiru

...