HEY HEY.. 2 'LIMIT's in one query!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

HEY HEY.. 2 'LIMIT's in one query!!

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: HEY HEY.. 2 'LIMIT's in one query!!

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: HEY HEY.. 2 'LIMIT's in one query!!

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
Post Reply