CODEWITHSUNDEEP
sqloracle-databasegroup-byora-00979
Nawaf
Nawaf

Reputation: 540

Using aggregate function to return minimum value

Please, help me to create a query to determine minimum date_time from the table below: 

 ID  |  Name  |  Date_Time | Location
---------------------------------------
 001 |  John  | 01/01/2015 |   901
 001 |  john  | 02/01/2015 |   903
 001 |  john  | 05/01/2015 |   905
 001 |  john  | 06/01/2015 |   904
 002 |  Jack  | 01/01/2015 |   903
 002 |  Jack  | 03/01/2015 |   904
 002 |  Jack  | 04/01/2015 |   905
 003 |  Sam   | 01/01/2015 |   904
 003 |  Sam   | 03/01/2015 |   903
 003 |  Sam   | 04/01/2015 |   901
 003 |  Sam   | 06/01/2015 |   903

I tried this query:

SELECT ID, NAME, MIN(DATE_TIME), LOCATION
  FROM TABLE
 GROUP BY (ID)

but I got this error message:

ORA-00979: not a GROUP BY expression

Upvotes: 0

Views: 103

Answers (4)

Ankit Agrahari
Ankit Agrahari

Reputation: 379

select t1.name,t1.id,t1.location,t1.date from (select id,MIN(Date) as min_date from table group by id ) t2 inner join TABLE t1 on t1.date=t2.min_date and t1.id=t2.id;

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

When you group something, all other rows will be left clustered to that grouped key. For a key, you can only fetch one of the row(entity) in SELECT.

Shortcut is, what ever in GROUP BY can be in SELECT freely. Otherwise, they have to be enclosed in a AGGREGATE function.

When you group by id,

001 key has 4 rows clustered to it.. Just think, what would happen when you specify non grouped column in SELECT. Where-as when you use MIN(date).. out of 4 dates, a minimum of one is taken.

So, your query has to be

SELECT ID,MIN(NAME),MIN(LOCATION),MIN(DATE)
FROM TABLE
GROUP BY ID

OR

SELECT ID,LOCATION,NAME,MIN(DATE)
FROM TABLE
GROUP BY ID,LOCATION,NAME

OR

Analytical approach.

SELECT ID,LOCATION,DATE,MIN(DATE) OVER(PARTITION BY ID ORDER BY NULL) AS MIN_DATE
FROM TABLE.

Still, it is upto the requirements, on how the query has to be re-written.

EDIT: To fetch rows corresponding the Min date, we can create a SELF JOIN like one below.

SELECT T.ID,T.NAME,T.LOCATION,MIN_DATE
FROM
 (
   SELECT ID,MIN(DATE) AS MIN_DATE
   FROM TABLE T1
   GROUP BY ID
 ) AGG, TABLE T
 WHERE T.ID = AGG.ID
 AND T.DATE = AGG.MIN_DATE

OR

SELECT ID,NAME,LOCATION,MIN_DATE
FROM
( 
  SELECT ID,
         NAME,
         LOCATION,
         MIN(DATE) OVER(PARTITION BY ID ORDER BY NULL) MIN_DATE,
         ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NULL) RNK
  FROM TABLE
)
WHERE RNK = 1;

Upvotes: 1

Keith John Hutchison
Keith John Hutchison

Reputation: 5277

Try grouping all the other columns ... and if the table is name 'table' try changing the table name to something else in the schema. 

SELECT ID , NAME , MIN(DATE_TIME) , LOCATION FROM TABLE  GROUP BY ID, Name, Location

Upvotes: 0

Martina
Martina

Reputation: 929

If you use aggregation function, you have specify for which fields the agregation should be applied. So you are using group by clause. In this case you probably mean to find the minimum date_time for each id, name combination.

 select id, name, min(date_time) 
              from my_table group by id, name

Upvotes: 1

Related Questions