MAX function question

• Apr 5, 2011

• #2

r937

Technical User

Jun 30, 2002

8,847

CA

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

 

Upvote 0 Downvote

• Apr 5, 2011

• #3

PHV

MIS

Nov 8, 2002

53,708

FR

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
FAQ181-2886

 

Upvote 0 Downvote

• Apr 6, 2011

• Thread starter
• #4

mark1110

Programmer

Apr 20, 2005

85

US

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"

 

Upvote 0 Downvote

• Apr 6, 2011

• #5

r937

Technical User

Jun 30, 2002

8,847

CA

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

 

Upvote 0 Downvote