CODEWITHSUNDEEP
mysql
CAPS LOCK
CAPS LOCK

Reputation: 2040

Select 5 rows if they are not in top 3

I want to select 5 articles from table that are not 3 newest.

Here is how I get 5 articles from the table named article:

SELECT * FROM article WHERE FOREIGN_ID_MENU = '".$id."' AND active = '1' ORDER BY date_created DESC LIMIT 0, 5;

And how I get the top 3 articles from the same table:

SELECT * FROM article WHERE active = '1' ORDER BY date_created DESC LIMIT 0, 3;

How can I combine those queries to select 5 articles that are not 3 newest? I do this with PHP.

Upvotes: 0

Views: 55

Answers (3)

saraf
saraf

Reputation: 84

If every article has an ID, you can try this :

SELECT 
  * 
FROM
  article 
WHERE FOREIGN_ID_MENU = '".$id."' 
  AND active = '1' 
  AND ID NOT IN 
  (SELECT 
    ID 
  FROM
    article 
  WHERE active = '1' 
  ORDER BY date_created DESC 
  LIMIT 0, 3) 
ORDER BY date_created DESC 
LIMIT 0, 5 ;

Upvotes: 1

CAPS LOCK
CAPS LOCK

Reputation: 2040

What I wanted was actually this:

SELECT * FROM article WHERE
FOREIGN_ID_MENU = '".$id."' AND active = '1' AND
date_created < (SELECT date_created FROM article ORDER BY date_created DESC LIMIT 3,1)
ORDER BY date_created DESC LIMIT 0, 5"

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use UNION and use braces for the queries to use the limit for each query

(SELECT * FROM article WHERE active = '1' 
AND FOREIGN_ID_MENU != '".$id."'
ORDER BY date_created DESC LIMIT 0, 3)
UNION
(SELECT * FROM article WHERE 
FOREIGN_ID_MENU = '".$id."' AND active = '1' 
ORDER BY date_created DESC LIMIT 0, 5)

EDIT from comments 

SELECT a.* FROM article a WHERE 
a.FOREIGN_ID_MENU = '".$id."' AND a.active = '1'
AND NOT EXISTS 
(SELECT 1 FROM article aa WHERE aa.active = '1' 
AND aa.id =a.id
ORDER BY aa.date_created DESC LIMIT 0, 3) 
ORDER BY a.date_created DESC LIMIT 0, 5

Upvotes: 1

Related Questions