MAX function question

[mark1110]

Hi,

I am writing a query that uses the max function. I have:

SELECT *
FROM myTable
WHERE term_dt = '01/01/1900' AND
bus_type <> "G"
GROUP BY id_num
HAVING effect_dt = max(effect_dt) AND co_id <> "001"
ORDER BY id_num

This seems to work, but I am not comfortable with it. Is there a better way of doing this query.

Thanks,

Mark

 

[r937]

the operative word being "seems"

this has gotta be mysql, right?

because in ANSI SQL (the forum you posted in), you are not allowed to get away with -- please don't take this personally -- something as sloppy as the dreaded, evil "select star" in a GROUP BY query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

What about this ?

SELECT A.*
FROM myTable A INNER JOIN (
SELECT id_num,MAX(effect_dt) AS LastDate FROM myTable 
WHERE term_dt='01/01/1900' AND bus_type<>'G' AND co_id<>'001'
GROUP BY id_num) M ON A.id_num=M.id_num AND A.effect_dt=M.LastDate
WHERE A.term_dt='01/01/1900' AND A.bus_type<>'G' AND A.co_id<>'001'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mark1110]

Would it be better to say the following:

SELECT field1, field2, field3, field4, effect_dt, field5, field6, term_dt, bus_type, co_id, field8, field9, field10
FROM myTable
GROUP BY field1, field2, field3, field4, effect_dt, field5, field6, term_dt, bus_type, co_id, field8, field9, field10
HAVING term_dt = '01/01/1900' AND bus_type <> "G" AND
effect_dt = max(effect_dt) AND co_id <> "001"

 

[r937]

no it wouldn't ;-)

non-aggregate conditions should be in the WHERE clause, not the HAVING clause

also, you can't do [red]effect_dt = max(effect_dt)[/red]

did you try PHV's query?


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon