Page 1 of 1
HEY HEY.. 2 'LIMIT's in one query!!
Posted: Thu Dec 22, 2005 10:51 am
by seodevhead
Hey guys, I have a quick MySQL question. I have a mysql query where I want to use a
WHERE condition like id!=3, but I only want this conditional to be in effect for a
certain number of records. In other words, in pseudo.. this is kinda what I'm looking for..
Code: Select all
$query = "SELECT id, blah blah blah FROM table (WHERE id!=3 LIMIT 5) AND blah blah AND blah blah LIMIT 20";
So essentially, I just want the first 3 records that match my (id!=3) where conditional to be
knocked off but allow the rest to be included in the results. I hope this makes sense because this has been hard trying to put into understandable words! Thanks for your time and efforts! Take care.
Posted: Thu Dec 22, 2005 11:40 am
by onion2k
This should work.. it's pretty complicated though..
Code: Select all
SET @mycount :=0;
SELECT *
FROM `table`
WHERE IF (
id =1, @mycount := @mycount +1, @mycount := @mycount +0
)
AND @mycount < 3
ORDER BY category_id;
Explained: Set up a local SQL variable called mycount. As the select statement is run it checks if id=1 .. if it does then it increments mycount. As soon as mycount is more than 3 it stops matching the @mycount < 3 bit so no more records are returned.
Re: HEY HEY.. 2 'LIMIT's in one query!!
Posted: Thu Dec 22, 2005 12:37 pm
by timvw
So essentially, I just want the first 3 records that match my (id!=3) where conditional to be knocked off but allow the rest to be included in the results. I hope this makes sense because this has been hard trying to put into understandable words! Thanks for your time and efforts! Take care.
Please notice that without an ORDER BY clause, LIMIT doesn't make much sense..
Code: Select all
SELECT id, blah blah blah
FROM table, blah blah blah , blah blah
WHERE id NOT IN (
SELECT id
FROM something
WHERE id = 3
LIMIT 3
)
LIMIT 20
Re: HEY HEY.. 2 'LIMIT's in one query!!
Posted: Thu Dec 22, 2005 1:12 pm
by onion2k
timvw wrote:Please notice that without an ORDER BY clause, LIMIT doesn't make much sense..
There's no such thing as a SELECT without an ORDER BY clause. Not specifying it is the same as using the default order on the table.
Posted: Thu Dec 22, 2005 3:05 pm
by timvw
A select without order by can make sense if you just want to get all rows that meet the requirements and don't care about the order in which they are returned. All that matters is that they are returned. The real problem is that there is no such thing as "default order" on a table.