Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Use of MAX function - right or wrong?

Re: Use of MAX function - right or wrong?

From: Kay Cee <cee.1_at_osu.edu>
Date: Thu, 10 Jul 2003 10:15:52 -0400
Message-ID: <bejseo$pke$1@charm.magnus.acs.ohio-state.edu>


Thank you for your help, and my apologies for the newsgroup faux pas. It won't happen again.

Kay

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:s44pgv0dl7h0seh5jcdb8imdo88pb8e4de_at_4ax.com...
> On Wed, 9 Jul 2003 17:17:58 -0400, "Kay Cee" <cee.1_at_osu.edu> wrote:
>
> >I'm trying to create an SQL query using a table with two fields. There
are
> >multiple date rows in column B associated with each row in column A. I
was
> >looking at using the MAX function to retrieve the most current date value
> >for each data row in column A, but the MAX function only retrieves a
single
> >data row with the most recent date, irrespective of the values in column
A.
> >Is there a way around this?
> >
> >Here's an example of my scenario:
> >
> >Item_No Date
> >====== ====
> >001 01/01/03
> >001 31/01/03
> >001 02/02/03
> >001 28/02/03
> >002 01/01/03
> >002 04/01/03
> >003 07/01/03
> >
> >Instead of retrieving three data rows with my query (one for each unique
> >Item_No), all I'm returning is the data row with Item_No 003 because it's
> >the one with the most current date. Can someone shed some light on where
I
> >went wrong? Here's one of my SQL statements:
> >
> >SELECT item_no, date FROM tablename
> >
> >WHERE date =
> >
> >(SELECT MAX(date) FROM tablename)
> >
> >
>
>
> SELECT item_no, date FROM tablename x
> WHERE date =
> (SELECT MAX(date) FROM tablename y where y.itemno = x.itemno)
>
>
> Please try to avoid posting homework and/or faqs
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu Jul 10 2003 - 09:15:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US