Re: Use of MAX function - right or wrong?

#1

On Wed, 9 Jul 2003 17:17:58 -0400, "Kay Cee" <cee.1 (AT) osu (DOT) edu> wrote:

Quote:

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

#2

"Kay Cee" <cee.1 (AT) osu (DOT) edu> wrote

Quote:

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)

Not only incorrect sql, but crap sql IMO. Why on earth just not use
plain sql instead as it *should* be used?

SELECT
item_no,
MAX(date) as MAX_DATE
FROM table
GROUP BY item_no
ORDER BY 1

ROW-BY-ROW processing (as what you are attempting) is a *BAD* *HABIT*.
It will bite you in the butt, kick you in the nuts, steal your credit
card and sleep with your wife/boyfriend/dog when you least expect it.

Always aim for processing _data_ with SQL. Not individual rows.

--
Billy

#3

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

Kay

"Sybrand Bakker" <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote

Quote:

On Wed, 9 Jul 2003 17:17:58 -0400, "Kay Cee" <cee.1 (AT) osu (DOT) 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

#4

"Kay Cee" <cee.1 (AT) osu (DOT) edu> wrote

Quote:

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?

look at how the GROUP BY clause works, it should help you out.

BTW didn't you used to have a sunshine band?

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Use of MAX function - right or wrong?

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Use of MAX function - right or wrong? - 07-09-2003 , 05:02 PM

Re: Use of MAX function - right or wrong? - 07-10-2003 , 03:56 AM

Re: Use of MAX function - right or wrong? - 07-10-2003 , 09:15 AM

Re: Use of MAX function - right or wrong? - 07-10-2003 , 04:08 PM