CODEWITHSUNDEEP
phpmysql
Andelas
Andelas

Reputation: 2052

MySQL Query to pull items, but always show a certain one at the top

I have a database where as items are entered in they're given a section such as "books", "dvds", "posters", "accessories", "clothing" etc. So each item has a section tacked onto it.

Here's the SQL query I'm using to find the unique sections so we can use them to organize the items by section. 

SELECT DISTINCT section FROM merch

That gives me the correct results, putting the unique sections into an array. Now, I'd like for two sections to always be listed first. So I always want "posters" and "books" first and second, then show the rest of the categories.

Is there a way to adjust the above query so that those two sections are shown first, then the remaining unique categories show up below?

Upvotes: 3

Views: 617

Answers (4)

Brian Hoover
Brian Hoover

Reputation: 7991

You could do this a couple of ways.

If there is dynamic data in the sections, the easiest is probably a union statement like

SELECT * FROM section WHERE id = 'books'
    UNION
SELECT * FROM section WHERE id = 'posters'
    UNION
SELECT * FROM section WHERE id IN (SELECT DISTINCT section_id 
                                      FROM merch 
                                      AND section_id not in ('books','posters'))

Upvotes: 0

Ryan
Ryan

Reputation: 1888

I use the ORDER BY FIELD() function when I do this in my application, see below

SELECT * FROM merch ORDER BY FIELD(section,'posters','books','dvds','accessories')

Upvotes: 2

mwhite
mwhite

Reputation: 2111

SELECT DISTINCT section FROM merch 
    ORDER BY FIELD(section, 'books', 'posters') DESC;

Upvotes: 4

alex
alex

Reputation: 490433

This should work...

SELECT DISTINCT `section`
           FROM `merch`
       ORDER BY FIELD(`name`, 'posters', 'books');

Upvotes: 0

Related Questions