CODEWITHSUNDEEP
mysqldate
Titus
Titus

Reputation: 55

MySQL ordering with two dates

I m looking for the way to order my mysql data query according the fact i use 2 dates (created and modified). Evidently, modified is set when there is a come back on created.

I would like to order first priority to modified if exists or created.

Clearly i would like to show persons that visited the last in date independantly of came or came back.

QUERY :

SELECT userfrom, idrelation_user_visite_user FROM relation_user_visite_user WHERE userto = 1 AND SUBSTR(created,1,10) < '2013-12-13' ORDER BY created, modified DESC LIMIT 50

(!!!) IMPORTANT : Just understood that i have to make a kind of array (theorical) grouping created and modified, THEN, order all DESC

SRC - SQL :

CREATE TABLE IF NOT EXISTS `relation_user_visite_user` (
  `idrelation_user_visite_user` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  `userfrom` int(11) DEFAULT NULL,
  `userto` int(11) DEFAULT NULL,
  `active` int(1) DEFAULT '1',
  PRIMARY KEY (`idrelation_user_visite_user`),
  KEY `fk_relation_user_visite_user_users1` (`userfrom`),
  KEY `fk_relation_user_visite_user_users2` (`userto`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1260 ;



INSERT INTO `relation_user_visite_user` (`idrelation_user_visite_user`, `created`, `modified`, `deleted`, `userfrom`, `userto`, `active`) VALUES
(61, '2013-12-13 15:26:08', NULL, NULL, 292, 1, 1),
(64, '2013-10-21 22:48:04', '2013-11-24 21:10:24', NULL, 292, 1, 1),
(93, '2013-10-23 13:24:30', '2013-11-09 16:06:47', NULL, 292, 1, 1),
(132, '2013-10-24 20:07:39', NULL, NULL, 292, 1, 1),
(202, '2013-10-27 21:40:29', '2013-11-15 00:58:37', NULL, 292, 1, 1),
(311, '2013-11-03 18:28:49', NULL, NULL, 292, 1, 1),
(379, '2013-11-06 13:40:50', NULL, NULL, 292, 1, 1),
(407, '2013-11-06 18:27:50', '2013-11-06 18:48:18', NULL, 292, 1, 1),
(521, '2013-11-09 16:06:36', '2013-12-10 19:16:21', NULL, 292, 1, 1),
(563, '2013-11-11 22:51:46', NULL, NULL, 292, 1, 1),
(722, '2013-11-18 05:08:09', '2013-11-18 05:21:37', NULL, 292, 1, 1),
(765, '2013-11-18 20:11:17', NULL, NULL, 292, 1, 1),
(859, '2013-11-23 01:04:11', '2013-12-10 18:48:50', NULL, 292, 1, 1),
(935, '2013-11-27 16:15:36', '2013-12-02 11:48:24', NULL, 292, 1, 1),
(982, '2013-12-01 18:00:48', NULL, NULL, 292, 1, 1),
(992, '2013-12-02 14:21:46', '2013-12-05 12:25:08', NULL, 292, 1, 1),
(1011, '2013-12-03 09:19:59', '2013-12-03 20:22:11', NULL, 292, 1, 1),
(1019, '2013-12-03 11:09:08', NULL, NULL, 292, 1, 1),
(1026, '2013-12-03 15:47:07', NULL, NULL, 292, 1, 1),
(1053, '2013-12-03 22:36:42', '2013-12-03 22:45:56', NULL, 292, 1, 1),
(1055, '2013-12-03 23:27:10', NULL, NULL, 292, 1, 1),
(1066, '2013-12-04 10:53:18', NULL, NULL, 292, 1, 1),
(1081, '2013-12-04 12:57:26', NULL, NULL, 292, 1, 1),
(1122, '2013-12-04 23:39:14', NULL, NULL, 292, 1, 1),
(1154, '2013-12-05 22:21:23', '2013-12-10 00:01:58', NULL, 292, 1, 1),
(1185, '2013-12-06 22:12:15', NULL, NULL, 292, 1, 1),
(1246, '2013-12-09 19:10:12', '2013-12-09 19:57:12', NULL, 292, 1, 1),
(1259, '2013-12-10 18:55:07', NULL, NULL, 292, 1, 1);

Upvotes: 0

Views: 1138

Answers (4)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

I assume when record is created them modified column is set to null so you can use CASE

SELECT userfrom, idrelation_user_visite_user
FROM relation_user_visite_user,
(CASE WHEN modified IS NOT NULL THEN modified 
ELSE created END) custom_order
WHERE userto = 1 
AND SUBSTR(created,1,10) < '2013-12-13' 
ORDER BY 
CASE WHEN modified IS NOT NULL THEN modified 
ELSE created END)
LIMIT 50

OR you can compare the columns

SELECT userfrom, idrelation_user_visite_user
FROM relation_user_visite_user 
WHERE userto = 1 
AND SUBSTR(created,1,10) < '2013-12-13' 
ORDER BY 
(CASE WHEN modified > created  THEN modified 
ELSE created END) DESC
LIMIT 50

Upvotes: 2

AdrianBR
AdrianBR

Reputation: 2588

use a case:

    SELECT userfrom, idrelation_user_visite_user ,
 case when modified is not null then modified else created end as last_visit
    FROM relation_user_visite_user 
    WHERE userto = 1 
    AND SUBSTR(created,1,10) < '2013-12-13' 
    ORDER BY 
    case 
    when modified is not null then modified 
    else created 
    end DESC 
limit 50

Upvotes: 2

Titus
Titus

Reputation: 55

SELECT IF( modified IS NOT NULL, modified, created ) as orderdatas,
created, modified, userfrom, idrelation_user_visite_user 
FROM relation_user_visite_user 
WHERE userto = 1
AND SUBSTR(created,1,10) < '2013-12-13'
ORDER BY orderdatas DESC LIMIT 50

Upvotes: -2

AgRizzo
AgRizzo

Reputation: 5271

I assume you can't use simple ORDER BY modified because sometimes modifed is NULL. If so, use

ORDER BY COALESCE(modified, created)

The COALESCE function returns the first non-null value.

Upvotes: 4

Related Questions