Check if a record exists in the last 30 records
Question posted by: djpaul (Member) on October 5th, 2008 03:06 AM
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
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
7 Answers Posted
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.
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.
No, it's like this:
ID - int - primary key - auto increment
songID int -
filename varchar
date_played - datetime
artist varchar
title varchar
...and furter
ID - int - primary key - auto increment
songID int -
filename varchar
date_played - datetime
artist varchar
title varchar
...and furter
This may not be the ideal way to do it, but this might work:
Then check mysql_num_rows to see if anything was returned.
Expand|Select|Wrap|Line Numbers
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.
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...
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...
Strange. I thought that worked in all versions 5 and above...
Anyhow. You could also try something like this:
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
Anyhow. You could also try something like this:
Expand|Select|Wrap|Line Numbers
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
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
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
Not the answer you were looking for? Post your question . . .
196,220 members ready to help you find a solution.
Join Bytes.com
196,220 members ready to help you find a solution.
Join Bytes.com
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 196,220 network members.Popular Articles
Top MySQL Contributors
You are viewing a mobilized version of this site...
View original page here
