How to get other values instead of first sorted values?

[#1]

Hi,

If there are values as below

id          status1           status2    DATE

234        US                  Region   23/01/2013

234        US                  GHY       01/12/2012

234        UK                   YUT       03/11/2012

how to get values instead of first sorted value. I need output as below

id          status1           status2    DATE

234        US                  Region    23/01/2013

234        UK                   YUT       03/11/2012

can anyone suggest how to do this?

Thanks.

[avinashelite]

use aggr function like this:

aggr(max(Date),id,status1)

[shair_abbas]

its easier to perform this task in script like

table1:
       Load

                ID,

                max(date) as Date

      from  xyz

                group by ID;

left join

        Load

              ID,

              Staus1,

              Status2,

              Date

      from xyz;

it is sort of self join.few days before i had encounter same issue that's how i get it resolved.

[#4]

Thanks. this is working only when the there are different dates. but when the dates are different it is not working. in the table date there is one ID with multiple same dates.

ID 35324 have below date values in Date column

15/01/2015

15/01/2015

20/02/2015

20/02/2015

29/11/2014

the date value I need is 15/01/2015. but this date is not showing. how to get latest date value even though If there are multiple same latest dates.

[PrashantSangle]

Hi,

in that case which date you are considering with which status2.

And how you distinguwish that data.? and which one is latest data??

Explain with example, so it will became easy to give solution.

Regards

[avinashelite]

As dreamer4‌ mentioned please explain with an example

[#7]

so wherever the status2='Region' I need maximum date

id          status1           status2    DATE

35324       US                  Region   15/01/2015 13:38:29

35324       TY                  Region   15/01/2015 13:37:29

35324        US                  GHY       01/12/2012 17:09:10

35324       TY                  Region   15/01/2015 10:14:36

35324        UK                   YUT       03/11/2012 10:30:23

I need output as below

id          status1           status2    DATE

35324        US                  Region    15/01/2015 13:38:29

35324        UK                   YUT        03/11/2012 10:30:23

[avinashelite]

so you want the grouping on status2?

can you please explain me why this 2 data got eliminated

35324        US                  GHY       01/12/2012 17:09:10

35324       TY                  Region   15/01/2015 10:14:36

[#9]

need only minimum of status1 date where

satus1 have minimum value and

status2='Region'  (latest date of status2) that is the reason the other two row got eliminated.