0

I have following data

ID  Status            Order Date
1   to be validated    1    2013/02/18 14:24:55,085000000
1   to be modified     2    2013/02/19 10:37:29,641000000
1   to be validated    3    2013/03/15 14:42:31,913000000
1   in force           4    2013/03/20 09:57:14,839000000
1   in force           5    2013/03/22 15:41:46,683000000
1   in force           6    2013/03/26 09:50:53,312000000
1   in force           7    2013/03/26 10:00:27,461000000
1   in force           8    2013/03/27 11:03:02,981000000
1   in force           9    2013/03/27 16:28:25,405000000
1   closed            10    2013/03/28 16:16:30,152000000
1   closed            11    2013/03/28 16:16:30,199000000
2   pending            1    2013/02/19 10:07:15,177000000
2   pending            2    2013/03/26 11:48:23,800000000
3   to be validated    1    2013/02/20 15:03:23,771000000
3   to be validated    2    2013/02/27 13:45:43,505000000
3   to be modified     3    2013/02/27 13:49:08,845000000
3   refused            4    2013/02/27 13:53:36,543000000
3   refused            5    2013/04/10 12:14:04,946000000
3   refused            6    2013/04/10 12:14:04,961000000

for which I would like to group by ID but get whole row only for the max(order) in a group, i.e.

1   closed            11    2013/03/28 16:16:30,199000000
2   pending            2    2013/03/26 11:48:23,800000000
3   refused            6    2013/04/10 12:14:04,961000000

I tried a group by statement, but am not sure what aggregate function would yield required results, i.e. get correct values for the remaining coulmns (out of group scope):

SELECT
  ID
, STATUS
, MAX(ORDER)
, DATE
FROM
  ....
GROUP BY 
  ID

How should correct query look like to achieve expected result?

Macin
  • 391
  • 2
  • 6
  • 20
  • I am not sure this is a duplicate of sugested question. I've seen it before asking, but will analyze it in a bit more detail – Macin Oct 20 '14 at 13:02
  • It is @Macin, to be explicit, `select id, max(status) keep (dense_rank last order by date) as date, max(order) keep (dense_rank last order by date) as order, max(date) as date from group by id`. I recommend [Rob van Wijk's](http://rwijk.blogspot.co.uk/2012/09/keep-clause.html) explanation. – Ben Oct 20 '14 at 19:22

0 Answers0