Re: Use of MAX function - right or wrong?

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 Wed Jul 09 2003 - 17:02:13 CDT