MAX function question
MAX function question
(OP)
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
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
RE: MAX function question
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
RE: MAX function question
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: MAX function question
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"
RE: MAX function question
non-aggregate conditions should be in the WHERE clause, not the HAVING clause
also, you can't do effect_dt = max(effect_dt)
did you try PHV's query?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon