Re: problems with picking up the latest record..

Deepu wrote:
> "Deepu" wrote:
> > Hi,
> > I have a table in my database which has a date column...I need
> > to pick up a resultset based on a where clause, identify the
> > latest record based on the date column and then go ahead and
> > pick up other values of the latest record. Here is the rough
> > structure of the query..How could we use max with a date??..or
> > could we do an order by...desc and pick up the first
> > record??..Please help..
> >
> > select bill_nbr from bill_table
> > where (bill_date is the most recent.)
> >
> > Thanks in Advance.
> > Deepu.
> > [color=blue:f44f5a1408][/color:f44f5a1408]
>
> HI,
> Got the answer by myself..
>
> select bill_nbr from (select bill_nbr from bill_table order by
> update_date desc) temp where rownum < 2.
>
> do write in your replies too...
>
> Thanks a lot,
> Deepu.

You had the answer in your own first post:
> How could we use max with a date??..

Why not try it and find out? Since you seem to like inline views try this:

select bill_nbr from

   ( select max(update_date) latest from bill_table ) temp,    bill_table
where temp.latest = bill_table.update_date ;

Stop thinking in terms of files and start thinking in terms of the data. ROWNUM is a crutch. Avoid it whenever possible. What made you think MAX() could not be used on a DATE column? It works fine on dates, number, even text columns.

And for kicks here's another way to phrase this query:

select bill_nbr from bill_table
  where update_date in
   ( select max(update_date) latest from bill_table ) ;

So use ROWNUM only as a last resort. It seldom is the best solution.

HTH,
 Ed Received on Tue Sep 13 2005 - 13:32:23 CDT