Order by Date until it hits a specific date and then order by id DESC

Question

Basically I have a site that offers premium users better position on the search features, and then the other users will be after the premium users:

CREATE TABLE IF NOT EXISTS `mlisting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `price` decimal(9,2) NOT NULL,
  `images` text NOT NULL,
  `live` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `mlisting` (id, cid, title, price, images, live) VALUES 
(1, 1, 'title 1', 29.99, '', 1),
(2, 2, 'title 2', 69.99, '', 1),
(3, 2, 'title 3',  1.99, '', 1),
(4, 1, 'title 4', 48.99, '', 1);

CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trust` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `companies` (id, trust) VALUES 
(1, '1970-01-01 00:00:00'),(2, '2019-01-01 00:00:00');

So the query should search where premiumdate >= todaysdate (premium expiry day) and then once no more results are found to then use the rest in the table.

I have tried UNION but that did not work on the order by, the premium users did not get priority listing.

Here is my query so far:

(
SELECT 
m.id, m.cid, m.title, m.price, m.images, c.trust 
FROM 
mlisting m 
LEFT JOIN 
companies c 
ON (m.cid = c.id) 
WHERE c.trust >='{$itsToday}' AND m.live=1 
ORDER BY m.id DESC
) 
UNION 
(
SELECT 
mo.id, mo.cid, mo.title, mo.price, mo.images, co.trust 
FROM 
mlisting mo 
LEFT JOIN 
companies co ON (mo.cid = co.id) 
WHERE co.trust='1970-01-01 00:00:00' AND mo.live=1 
ORDER BY mo.id DESC
)

Thanks for taking a look and hopefully pulling me in the right direction.

Here is a SQL Fiddle to show what I mean by the ORDER BY not working: SQL Fiddle

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 14 '18 at 21:56
I have updated so you can see what I mean by the order by not working: http://sqlfiddle.com/#!9/a911a3/5 — Robert, May 14 '18 at 22:17

score 1 · Answer 1 · answered May 14 '18 at 22:37

1

You can use a CASE in the ORDER BY expression, that returns a higher (lower) number depending on the condition. Use this as first expression to order by and, if you like, the ID as second.

SELECT m.id,
       m.cid,
       m.title,
       m.price,
       m.images,
       c.trust 
       FROM mlisting m 
            LEFT JOIN companies c 
                      ON m.cid = c.id 
       WHERE m.live=1 
       ORDER BY CASE
                  WHEN c.trust >= current_date()
                    THEN 0
                  ELSE 1
                END ASC,
                m.id DESC;

(And a UNION might not work because this eliminates duplicates and the DBMS might want to reorder the results to do so. Maybe a UNION ALL would have worked but then again there is no guarantee for that.)

answered May 14 '18 at 22:37

sticky bit

36,626
12
31
42

I for some reason can not use this code, even though it seems to work on the fiddle, I get the following error: Warning: PDO::query(): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll() – Robert May 14 '18 at 23:02
@Robert: That sounds like some PHP issue (or how you execute queries in there) unrelated to the query itself and like it's out of the scope of this question. Maybe though this helps: https://stackoverflow.com/questions/17434102/causes-of-mysql-error-2014-cannot-execute-queries-while-other-unbuffered-queries – sticky bit May 14 '18 at 23:08

LukStorms · Accepted Answer · 2018-05-17T14:05:43.417

When you do a union of selects, the ORDER BY at the end will sort the combined resultset.

So you can simplify it:

SELECT m.id, m.cid, m.title, m.price, m.images, c.trust 
FROM mlisting m 
JOIN companies c ON (m.cid = c.id) 
WHERE m.live = 1 
  AND c.trust >= current_date 

UNION ALL

SELECT m.id, m.cid, m.title, m.price, m.images, c.trust 
FROM mlisting m
LEFT JOIN companies c ON (m.cid = c.id) 
WHERE m.live = 1 
  AND nullif(c.trust,'1970-01-01 00:00:00') is null

ORDER BY (case when c.trust >= current_date then 0 else 1 end), id DESC

Note that a UNION ALL was used, instead of a UNION. Since there shouldn't be a need to discard duplicates when the dates don't overlap anyway between the 2 selects.

Afternote:

This could also be done without a UNION ALL.
Since the different date criteria can be added to a single SELECT.

SELECT m.id, m.cid, m.title, m.price, m.images, c.trust 
FROM mlisting m 
LEFT JOIN companies c ON (c.id = m.cid) 
WHERE m.live = 1
  AND
  (     c.trust >= current_date
     OR c.trust = '1970-01-01 00:00:00'
     OR c.trust IS NULL
  )
ORDER BY (case when c.trust >= current_date then 0 else 1 end), id DESC

Hi @LukStorms , thanks for taking time to take a look, I am not wanting it to order by the trust, I want it to order by ID DESC on both, just making the trust come first while still order by id desc — Robert, May 14 '18 at 22:50
Oh. Must have misunderstood the title then. Guess it needs a CASE in the order by instead. — LukStorms, May 14 '18 at 22:58
Thank you so much, I need to do some reading on the nullid and the case when , I have never seen these before in sql. Thanks :) — Robert, May 14 '18 at 23:09

Order by Date until it hits a specific date and then order by id DESC

2 Answers2