CODEWITHSUNDEEP
mysql
Sharpeye500
Sharpeye500

Reputation: 9071

Identify values based on date and limit

I have output in a list of values.

Name  Date of Entry
C1 - 1/1/2011
C3 - 3/3/2011
C2 - 2/2/2011    
C4 - 4/4/2011
C5 - 5/5/2011

For say the above list count is 5, i have cap count as 2, so any 'C's greater than 2 should be disabled and the Cs that are going to be disabled will be based on date of entry.

ie. take out the Cs that are older based on date (first entered into the system), in this case C1 and C2 should be enabled and C3, C4 & C5 should be disabled.

select * from table order by date; => This gives me C1,C2,C3,C4,C5.

select * from table order by date limit 2; => This gives me C1 and C2 which is great, but how do i know C3,C4,C5 need to be disabled.

I do want to identify C3,C4 and C5 need to be disabled.

Is it possible to do in a single query? Any mysql developers any help?

Thanks.

Upvotes: 1

Views: 90

Answers (1)

Mark Byers
Mark Byers

Reputation: 839114

Assuming that the date is unique, try this:

SELECT
    name,
    date,
    date >
    (
        SELECT MIN(date)
        FROM your_table
        WHERE date > (SELECT MIN(date) FROM your_table)
    ) AS disabled
FROM your_table
ORDER BY date

Upvotes: 2

Related Questions