hi can anyone see what is wrong with this syntax? it is driving me mad!! thanks in advance
SELECT MAX(caldate) AS Date
SELECT TOP 5 caldate
WHERE caldate > date add(day, DATEDIFF (day, 0, curdate()), 0)
and WORKINGDAY = 'Y'
ORDER BY caldate
it's simple, really
mysql doesn't support TOP
and the DATEADD and DATEDIFF functions are different, too
are you sure you're using mysql?
hah thanks yeah i have just realised i should be using limit...
very new to mysql i am used to mssql...
do you think you could convert the above query to work with mysql?
think i have figured it out...
select caldate from calendar where caldate =
(select max(caldate) from (select caldate from calendar where caldate >now() and workingday = 'Y' limit 5)c)
i could convert it if i understood it
in the subquery, the WHERE clause appears to be filtering on caldate so that only values greater than today are retrieved
then you sort these into ascending sequence and take the first 5
and then in the outer query, you're taking the 5th one
in mysql, you can do it like this --
LIMIT 4,1 skips over 4 rows and returns the next 1
SELECT caldate AS Date
WHERE caldate > CURRENT_DATE
AND workingday = 'Y'
BY caldate LIMIT 4,1
is caldate a DATE or a DATETIME column?