Help on SQL with date comparions 1

[malaygal]

I have these two fields on my Oracle 10G table


User Date_Updated

A32NY 05/08/2008
A32FL 05/07/2008
A32CA 05/09/2008
A31AK 05/06/2008


I need to return the User and the latest Date_updated based on the 1st three characters of User (this is our job category), in this case A32CA 05/06/2008.

Any help will be greatly appreciated.

 

[SantaMufasa]

I need to return the User and the latest Date_updated based on the 1st three characters of User (this is our job category), in this case A32CA 05/06/2008.

Did you mean, instead, 05/09/2008?


If that is what you meant, then code to return "A32CA 05/09/2008" could be:

Select user,date_updated
  from <table_name>
 where date_updated = (select max(date_updated)
                         from <table_name>);

Let us know if this is what you wanted, or that I misunderstood your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[malaygal]

Thanks for the quick response.

"Did you mean, instead, 05/09/2008?" is exactly what I meant.

It worked fine, but I need to return

A32CA 05/09/2008
A31AK 05/06/2008

which has the latest date_update based on the first three character of the user column.

 

[SantaMufasa]

malaygal,

If that is what you want, it is even simpler:

select user,max(date_updated)
  from <table_name>
 group by user;

Try that and let us know if that is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Ooops, sorry. I forgot about the restriction that the user has a state component. I'll re-code for that then I'll post the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Here is the revised code (including the correction of "user" to "username"):

select username, date_updated
  from malaygal x
 where date_updated =
       (select max(date_updated)
          from malaygal y
         Where substr(x.username,1,3) = substr(y.username,1,3));

USERN DATE_UPDA
----- ---------
A32CA 09-MAY-08
A31AK 06-MAY-08

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[malaygal]

IT WORKED PERFECTLY.

i could not thank you enough.