-1

After querying with 

partition by Id order by Id asc on my table Tbl

I get the result:

Id      DATE&TIME               DATE       VALUE
A      03-05-2015,03:05:36  03-05-2015  2
B      19-05-2015,11:05:02  19-05-2015  2
A      15-05-2015,06:05:38  15-05-2015  2
A      15-05-2015,09:05:06  15-05-2015  8
A      15-05-2015,09:05:29  15-05-2015  10
A      15-05-2015,11:05:18  15-05-2015  10
c      05-05-2015,02:05:15  03-05-2015  20
d      12-05-2015,03:05:28  08-05-2015  2
d      12-05-2015,04:05:56  07-05-2015  10
e      04-05-2015,08:05:41  03-05-2015  2
f      06-05-2015,09:05:35  08-05-2015  20

but I require output like:

Id    DATE&TIME             VALUE
A     03-05-2015,03:05:36   2
B     19-05-2015,11:05:02   2
A     15-05-2015,11:05:18   10
c     05-05-2015,02:05:15   20
d     12-05-2015,04:05:56   10
e     04-05-2015,08:05:41   2
f     06-05-2015,09:05:35   20

i.e the record should be unique with respect to 'DATE' only & get maximum of 'VALUE' from above data.

beerbajay
  • 19,652
  • 6
  • 58
  • 75
srinivasA
  • 1
  • 1

2 Answers2

0

It seems like you want functionality similar to postgres' DISTINCT ON.

Per my previous answer, you can use:

select distinct id, 
first_value(datetime) over (partition by id order by value desc)
from mytable

This is making the assumption that you do not want order by id asc since for the same ids (e.g. A) the order will be unspecified; in the example given the results are therefore sorted by value desc.

Community
  • 1
  • 1
beerbajay
  • 19,652
  • 6
  • 58
  • 75
0

Have you tried using SELECT DISTINCT ID, TIME, VALUE FROM (partition by Id & order by Id asc on my table Tbl)

I'm not sure if that syntax works for OracleDB...

The SELECT - Parameters in my query should return the desired columns.

As for the TIME vs. DATE&TIME: If you take a closer look your rows are distinct if you select by DATE&TIME your desired result indicates, that you want DISTINCT entries for the DATE...

Christian Wirth
  • 123
  • 1
  • 9