Below is my column
1-1
1-2
2-1
2-4
Is It possible to sort in this way - First Column should be be ascending and Second column should be descending
So the result will be
1-4
1-2
2-1
2-1
I don't care if the row mismatch. How can I achieve this (Something like view)
CREATE TABLE IF NOT EXISTS `pipo_orders` (
`ClientID` int(8) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT ,
);
--
-- Dumping data for table `pipo_orders`
--
INSERT INTO `pipo_orders` (`ClientID`, `created_at`, `updated_at`) VALUES
(17, '2014-11-26 16:21:36', '2014-11-26 10:51:36'),
(17, '2014-11-26 16:46:24', '2014-11-26 11:16:24'),
(17, '2014-12-04 16:45:28', '2014-12-04 11:15:28'),
(17, '2014-12-26 12:58:13', '2014-12-26 07:28:13'),
(17, '2014-12-30 14:29:31', '2014-12-30 08:59:31'),
(17, '2014-12-30 14:31:05', '2014-12-30 09:01:05'),
(17, '2015-01-02 12:20:54', '2015-01-02 06:50:54');
If this is what you want it's weird, and I can't think of a reason why you'd want it. In fact, it goes against the fundamental logic of a database... Nevertheless:
SELECT t1.ClientID
, t1.created_at
, t2.updated_at
FROM pipo_orders t1
JOIN pipo_orders t2
ON 1 = 1
ORDER BY t1.created_at ASC
, t2.update_at DESC
That looks like a horrible thing to do. The values created_at and updated_at are related by being in the same row. You override this relation and join completely unrelated values.
However to technically do this, you would have to order the two columns separately (giving them row numbers) and then join them again:
select
col1.created_at,
col2.updated_at
from
(
select created_at, @rn1 := @rn1 + 1 as rownum
from pipo_orders po
cross join (select @rn1 := 0) as r
order by created_at
) as col1
from
(
select updated_at, @rn2 := @rn2 + 1 as rownum
from pipo_orders po
cross join (select @rn2 := 0) as r
order by updated_at desc
) as col2 using (rownum)
order by rownum;