Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Use of MAX function - right or wrong?
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) Received on Wed Jul 09 2003 - 16:17:58 CDT