Sub Query

[Guest]

Hi,

I have a group of records that I need to create a sub query by I don't know
how to go by doing it.

Say I have the following group records. I need to pull the record with the
latest date. However, the max function does not work. I still receive the
duplicate records. I think I need to create a SubQuery to will get the
latest date first.

ACCOUNT AMT DATE_ADDED
0000992057 68000 5/24/2005
0000992057 55000 10/20/2004
0001010039 489000 12/22/2003
0001010039 587000 8/18/2004

Any help from you guys would help.

 

[Tom Ellison]

Dear Jose:

The MAX() function works by finding the maximum value of a given column. It
does not limit the query to the row that has this maximum value. But you
can, with a subquery, as you suggest.

SELECT ACCOUNT, AMT, DATE_ADDED
FROM YourTable T
WHERE DATE_ADDED =
(SELECT MAX(DATE)
FROM YourTable T1
WHERE T1.ACCOUNT = T.ACCOUNT)

You must substitute the actual name of your table where I have used
YourTable.

If you have two rows for the same account with the same DATE_ADDED and that
DATE_ADDED is the most recent, then the query will return both. You need to
be aware of this, even if the query does not return any cases of this now.
It may do so later. If it is the case that no account may have two entries
for the same date, then it would be a good idea to constrain the data with a
unique index to this effect.

Does this help?

Tom Ellison

 

[Guest]

Thanks Tom.

It worked, that was guidance I need it.