SQL Query Advice - Writing to find two max values in one query

[Chris100682]

Hi - I need assistance using the max condition in SQL.

When I run the below sql query (App 1) I have returned the table of information displayed in (App 2)

App 1:
SELECT a1.group_code, a1.prod_code, a1.add_date, a2.number, a2.eff_date
FROM table1 a1, table2 a2
WHERE a1.prod_code = a2.prod_code

App 2:
group_code|prod_code|add_date|number|eff_date
AAAD | 2 |01/04/06 | 1 |02/03/06
AAAD | 3 |01/06/05 | 3 |06/06/05
AAAD | 4 |01/06/05 | 7 |01/08/07

As you can see I have duplicate group_codes returned, how do I write in to my query to bring back the record that shows the max add_date and the max eff_date (so in this example I would of like to have been shown the third record)
If anyone can help that would be great!
Thanks in advance
Chris

[OBonio]

What if the record with the max add_data isn't the same as the record with the max eff_date? Should the query return nothing?

[Loosehead]

You can't have 2 max values in one query, ie if they are different which record do you want?

you can use the "ORDER BY" to get the record you want at the top but that will be a different record, potentially, depending on which field you choose. In your example record 1 has the highest add_date and record 3 the highest eff_date, which record would you want. Yes I know you said 3 but did you really mean that?

[OBonio]

1st solution. This takes your example literally and will only return records that ARE the max eff_date AND max add_date. This doesn't return any records for your example data as record 1 has the max add_date and record 3 has the max eff_date.

SELECT a.*
FROM Table1 a
WHERE a.add_date=(SELECT MAX(add_date) FROM Table1 b WHERE b.group_code=a.group_code)
AND a.eff_date=(SELECT MAX(eff_date) FROM Table1 c WHERE c.group_code=a.group_code)

[OBonio]

2nd solution. This returns the max add_date and max eff_date for a particular group_code. ie, it returns the add_date from record 1 and the eff_date from record 3.

SELECT DISTINCT
a.group_code,
(SELECT MAX(add_date) FROM Table1 b WHERE b.group_code=a.group_code) AS max_add_date,
(SELECT MAX(eff_date) FROM Table1 b WHERE b.group_code=a.group_code) AS max_eff_date
FROM Table1 a