CODEWITHSUNDEEP
phpmysqlsql
tvgemert
tvgemert

Reputation: 1486

MYSQL query select from table and order by date and other field. prioritize other field

I have the following table called Agenda:

ID:visible:sticky:startdate
 1:      1:     1:2014-03-08
 2:      1:     0:2014-02-25
 3:      1:     0:2014-02-27
 4:      1:     0:2014-02-20
 5:      1:     0:2014-03-12

(ID->int, visible->tinyint, sticky->tinyint, startdate->date)

I am running the following query to get the next three agenda items by date, working fine of course. Returning ID's 4,2,3

  SELECT * 
    FROM agenda 
   WHERE visible 
     AND startdate >= "2014-02-12" 
ORDER BY startdate ASC 
   LIMIT 3

But what I would like is to prioritize fields that have sticky = 1, if there are fields that have it set they should be in the results and be accounted for in the ORDER BY. So what I would like it to return is ID's 4,2,1

How can this be achieved?

Upvotes: 1

Views: 414

Answers (2)

xzag
xzag

Reputation: 604

SELECT *
FROM (
  SELECT * FROM 
  agenda
  WHERE visible
  AND startdate >= "2014-02-12"
  ORDER BY sticky DESC, startdate ASC
  LIMIT 3
) subquery
ORDER BY startdate ASC

Upvotes: 2

Joostkam
Joostkam

Reputation: 21

this will set the sticky at the top of the results, as far as I know there is no way to include them midway, you could always use 2 query's and combine the results in an array which you could then sort by date.

SELECT *
FROM agenda
WHERE visible
AND startdate >= "2014-02-12"
ORDER BY sticky DESC , startdate ASC
LIMIT 3

Upvotes: 0

Related Questions