CODEWITHSUNDEEP
mysql
Newbee123
Newbee123

Reputation: 61

Sort data ASC priority to data not = 0

How to Seperate data = 0 from data not = 0, then sort data not = 0 ASC, and data = 0 at the last.

CREATE TABLE `fruit` (
`item` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`type` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`price` int(6) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Dumping data for table `fruit`

INSERT INTO `fruit` (`item`, `type`, `price`) VALUES
('Apple', 'Grade A', 10),
('Apple', 'Grade B', 8),
('Banana', 'Grade A', 15),
('Banana', 'Grade B', 0),
('Banana', 'Grade C', 8),
('Mango', 'Grade A', 0),
('Mango', 'Grade B', 12);
-- Indexes for dumped tables
-- Indexes for table `fruit`

ALTER TABLE `fruit`
ADD PRIMARY KEY (`item`,`type`) USING BTREE;

I try to sort by:

SELECT * FROM `fruit`
ORDER BY price DESC, item ASC

The result become: 

('Banana', 'Grade A', 15),
('Mango',  'Grade B', 12),
('Apple',  'Grade A', 10),
('Apple',  'Grade B',  8),
('Banana', 'Grade C',  8),
('Banana', 'Grade B',  0),
('Mango',  'Grade A',  0),

Actuall I just want to push those price = 0 to that last, those prices which is not = 0 will follow primary index

I want result like this:

('Apple',  'Grade A', 10),
('Apple',  'Grade B',  8),
('Banana', 'Grade A', 15),
('Banana', 'Grade C',  8),
('Mango',  'Grade B', 12),
('Banana', 'Grade B',  0),
('Mango',  'Grade A',  0),

How can I do it??

Upvotes: 1

Views: 51

Answers (3)

Virgil Ionescu
Virgil Ionescu

Reputation: 337

Try this

SELECT * FROM `fruit`
ORDER BY if(price=0,0,1) DESC,price DESC, item ASC

Upvotes: -2

JIJOMON K.A
JIJOMON K.A

Reputation: 1280

Try this code 

  SELECT * FROM `fruit`
    ORDER BY price=0,item asc

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You need to use conditional order by. Here is the SQL fiddle

Upvotes: 2

Related Questions