2

I need to have the results of a query show featured items first for a given date before listing the day's other items. You can ignore the categories that are being pulled from another table.

Currently the results list all of the events by date. There is a column in the eventlisting table that is called featured and has values 1 (featured) or 0 (non-featured). I want the exact same results except that all featured items for that day will list before the non-featured items, for each day. Thank you for any suggestions.

SELECT a.*, c.id as catid, c.cat_name FROM eventlisting a 
LEFT JOIN categories c ON a.cat_num=c.id 
WHERE a.startDate >= "2015-08-21" 
AND a.endDate <= "2016-08-21" 
AND a.status=1 
GROUP BY a.id 
ORDER BY a.endDate 
ASC LIMIT 0, 50

If this is my table data:

id featured status  startDate    endDate
_________________________________________
1      0      1    2015-08-21  2015-08-21
2      0      1    2015-08-21  2015-08-22
3      1      1    2015-08-21  2015-08-25
4      0      0    2015-08-21  2015-08-23
5      1      1    2015-08-22  2015-08-27
6      0      1    2015-08-22  2015-08-31
7      1      1    2015-08-22  2015-08-31

Then I need the results to show this order:

id featured status  startDate    endDate
_________________________________________
3      1      1    2015-08-21  2015-08-25
1      0      1    2015-08-21  2015-08-21
2      0      1    2015-08-21  2015-08-22
4      0      0    2015-08-21  2015-08-23
5      1      1    2015-08-22  2015-08-27
7      1      1    2015-08-22  2015-08-31
6      0      1    2015-08-22  2015-08-31
  • I've edited your question in an attempt to remove (or at least reduce) any ambiguity. Please take a look, and feel free to edit further or roll back my changes as necessary. Thank you. – Andriy M Aug 22 '15 at 9:03
1

All you need to do is ORDER BY startDate ASC first, then featured DESC:

SELECT 
    a.* --,
    -- c.id as catid, 
    -- c.cat_name 
FROM eventlisting a 
-- LEFT JOIN categories c ON a.cat_num=c.id 
WHERE a.startDate >= '2015-08-21'
AND a.endDate <= '2016-08-21' 
AND a.status=1 
GROUP BY a.id 
ORDER BY a.startDate ASC,
         a.featured DESC
LIMIT 0, 50;

Note: I've commented the categories table because you didn't specified which is its structure.

Test:

mysql> SELECT  a.*            FROM eventlisting a   WHERE a.startDate >= '2015-08-21' AND a.endDate <= '2016-08-21'  AND a.status=1  GROUP BY a.id  ORDER BY a.startDate ASC,  a.featured DESC LIMIT 0, 50;
+----+----------+--------+------------+------------+
| id | featured | status | startDate  | endDate    |
+----+----------+--------+------------+------------+
|  3 |        1 |      1 | 2015-08-21 | 2015-08-25 |
|  1 |        0 |      1 | 2015-08-21 | 2015-08-21 |
|  2 |        0 |      1 | 2015-08-21 | 2015-08-22 |
|  5 |        1 |      1 | 2015-08-22 | 2015-08-27 |
|  7 |        1 |      1 | 2015-08-22 | 2015-08-31 |
|  6 |        0 |      1 | 2015-08-22 | 2015-08-31 |
+----+----------+--------+------------+------------+
6 rows in set (0.00 sec)

mysql> 

Test it in this Fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.