Re: Use of MAX function - right or wrong?

"Kay Cee" <cee.1_at_osu.edu> wrote

> 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