Check if a record exists in the last 30 records

[djpaul]

Hello!
I want to check if a record exists in the last 30 records that were added.
For example, i have a table wich contains about 34000 records by now.
But i want to check if the songID is in the last 30 records.

Is this somehow possible?

Now i select the last 30 records and put them in an array, and let php check if this songID is in the array.
But it must be much easier if mysql this does!

Regards!
Paul

[Atli]

Hey Paul.

How does your table look like?
Is the "songID" column the primary key for that table?

I would be very surprised if this isn't possible, but without knowing exactly how you table looks like I can't really say for sure.

[djpaul]

No, it's like this:

ID - int - primary key - auto increment
songID int -
filename varchar
date_played - datetime
artist varchar
title varchar
...and furter

[labmonkey111]

This may not be the ideal way to do it, but this might work:

SELECT songID FROM songs WHERE songID IN (SELECT songID FROM songs ORDER BY ID DESC LIMIT 0, 30);

Then check mysql_num_rows to see if anything was returned.

[djpaul]

Hmm, that may work.
I gonna try it tomorrow and let you know!

Thanks!

[djpaul]

Hmm, too bad!
I get an error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I just updated mysql from 5.0.21 to 5.1.28 but it didn't seem to help.
Strange...

[Atli]

Strange. I thought that worked in all versions 5 and above...

Anyhow. You could also try something like this:

SELECT COUNT(`ID`) FROM `myTbl`
WHERE `songID` = 1
AND `ID` > (
    SELECT `ID` FROM `myTbl`
    ORDER BY `ID` DESC
    LIMIT 30, 1
)
 

The idea is to fetch the ID of the 30'th row from the top using the subquery and match only against rows with higher ID's.

Tested this on version 5.0.51

[djpaul]

Aahh!
That's a lot better.
It maybe a bug of mysql, but i think it's strange that you cannot use a limit in a IN function.
Too bad!

Thanks Atli!

Paul