MAX function question

Question

MAX function question

mark1110 (Programmer)

(OP)

5 Apr 11 18:20

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 (TechnicalUser) · Answer 1 · 2011-04-05 19:15:20

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 (MIS) · Answer 2 · 2011-04-06 02:55:22

What about this ?

CODE

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

mark1110 (Programmer) · Answer 3 · 2011-04-06 12:35:01

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 (TechnicalUser) · Answer 4 · 2011-04-06 12:47:40

no it wouldn't

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

Come Join Us!

Posting Guidelines

MAX function question