Skip to main content
W
wam4
Inspiring
September 19, 2008
Answered

query not pulling max date

  • September 19, 2008
  • 4 replies
  • 1328 views
I'm trying to get the highest date within a table and the corresponding records. Instead it pulls all the dates. I'm using max and grouped by id. Instead of it displaying the ID 10465 once it shows up twice. Same thing for 10860 and 1831. There should only be three records not 7 displayed.

I'm using CF7 with a sybase database Adaptive Server Enterprise version 12.0.0.8.

Thanks for the help.
This topic has been closed for replies.
Correct answer paross1
Does this give you something like the results that you are looking for?

<cfquery datasource="#dsn#" name="maxdate">
SELECT DISTINCT t1.EFFDT as date,
t1.EMPLID,
t1.DESCR
FROM titles AS t1
WHERE t1.EMPLID IN ('10465', '10860', '1831')
AND t1.EFFDT = (SELECT MAX(t2.EFFDT)
FROM titles AS t2
WHERE t1.EMPLID = t2.EMPLID)
</cfquery>

Phil

4 replies

W
wam4Author
Inspiring
September 19, 2008
Paross1 - that did the ticket - It's now getting the correct information.

Thanks Dan and Paross1 !
W
wam4Author
Inspiring
September 19, 2008
If I run the query below (using SQL Advantage -not on a CF page) I get an error message stating incorrect syntax near ',' But if I run everything within the brackets by itself it pulls out the data I would like to select. So I tried adding that directly into another SQL statement as in the one below.

Select ID, DESCR

from titles

where EFFDT =
(select max(EFFDT), ID
from titles
group by ID)
P
paross1
Participating Frequently
September 20, 2008
FYI, just for future reference, the reason that this......

Select ID, DESCR
from titles
where EFFDT =
(select max(EFFDT), ID
from titles
group by ID)

....didn't work is because you are selcting 2 items in your sub-select, so you are essentially saying

WHERE EFFDT = max(EFFDT), ID

which is never going to happen. Also, because you are not correlating your outer query with your inner query (relating the ID from the outer with the innner), you are essentially selecting the maximum EFFDT for any ID, and not for each distinct one for which you are attempting to find the maximum date. Make sense?

Phil
P
paross1Correct answer
Participating Frequently
September 19, 2008
Does this give you something like the results that you are looking for?

<cfquery datasource="#dsn#" name="maxdate">
SELECT DISTINCT t1.EFFDT as date,
t1.EMPLID,
t1.DESCR
FROM titles AS t1
WHERE t1.EMPLID IN ('10465', '10860', '1831')
AND t1.EFFDT = (SELECT MAX(t2.EFFDT)
FROM titles AS t2
WHERE t1.EMPLID = t2.EMPLID)
</cfquery>

Phil
D
Dan_Bracuk
Inspiring
September 19, 2008
You have to do it like this:

select somefields
from sometables
where somedatefield =
(select max(date) etc)
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices