By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,393 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,393 IT Pros & Developers. It's quick & easy.

Check if a record exists in the last 30 records

100+
P: 137
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
Oct 5 '08 #1
Share this Question
Share on Google+
7 Replies


Atli
Expert 5K+
P: 5,058
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.
Oct 5 '08 #2

100+
P: 137
No, it's like this:

ID - int - primary key - auto increment
songID int -
filename varchar
date_played - datetime
artist varchar
title varchar
...and furter
Oct 5 '08 #3

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

Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 5 '08 #4

100+
P: 137
Hmm, that may work.
I gonna try it tomorrow and let you know!

Thanks!
Oct 5 '08 #5

100+
P: 137
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...
Oct 7 '08 #6

Atli
Expert 5K+
P: 5,058
Strange. I thought that worked in all versions 5 and above...

Anyhow. You could also try something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(`ID`) FROM `myTbl`
  2. WHERE `songID` = 1
  3. AND `ID` > (
  4.     SELECT `ID` FROM `myTbl`
  5.     ORDER BY `ID` DESC
  6.     LIMIT 30, 1
  7. )
  8.  
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
Oct 7 '08 #7

100+
P: 137
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
Oct 8 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.