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 -> Use of MAX function - right or wrong?

Use of MAX function - right or wrong?

From: Kay Cee <cee.1_at_osu.edu>
Date: Wed, 9 Jul 2003 17:17:58 -0400
Message-ID: <bei0q6$hs5$1@charm.magnus.acs.ohio-state.edu>


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

Original text of this message

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