SQL: filter by date

Question

Hi,

I have a table SIGNUPS, where I register all signups to a specific event. Now, I would like to get all people who signed up to an event, with an extra column STATUS telling if the user is actually accepted (STATUS = "OK") or if it is in a waiting list (STATUS="WL"). I tried something like this

 SELECT *, IDUSER IN (SELECT IDUSER FROM SIGNUPS ORDER BY DATE ASC LIMIT 10)
 as STATUS from SIGNUPS WHERE IDEVENT = 1 

This should return STATUS 1 for the first 10 users who signed up, and 0 for all other ones. Unluckily, I get a Mysql error telling me that LIMIT in subqueries is not yet supported.

Could you please suggest another way to get the same information?

Thanks

Answer 1

Something like the following will get what you need - although I haven't tested it against some sample tables. The subqueries find the date above which the last ten signups occur, which is then used to comapre to the date of the current row.

select 
    s.*, 
    s.DATE > d.min_date_10 AS STATUS
from SIGNUPS s
    join (
        select MIN(DATE) AS min_date_10 from (
            select DATE from SIGNUPS order by DATE asc LIMIT 10
        ) a
    ) d
WHERE IDEVENT = 1