CODEWITHSUNDEEP
phpmysqlsorting
David Lawrence
David Lawrence

Reputation: 655

Sorting MYSQL results by multiple columns

OK, so I have gone through 14 Stack Overflow suggestions that were suggested when I wrote this question, and I have tried everything, and can not figure this out.

I have a gym directory with reviews. I have a script that you can search through those gyms by Zip code, City/State or Neighborhood/City/State.

I have a few fields in the back end and in the database. The ones pertaining to this question are priority (I give it a number 1+ and it should show highest first or lowest first I don't care which preferably highest to lowest), photo (it can have 5 photos), membership includes (a few things that the gym can include in the membership), and reviews (not editable but keeps count of how many review the gym has)

I would like to sort in this order

If the gym has priority, it should show first as it is prioritized. Null Priority should come last, Then sort by photo doesn't matter a-z z-a just null comes last, then sort by membership includes null comes last, then sort by review count 0 or null comes last.

So if I have 4 gyms, A and B with priority, photo, and membership but 0 reviews, C with no priority, no photo, no membership, but highest review count at 2, and D with no priority but has photo and membership, but 1 review: it should sort in this order:

GYM   Priority   Photo      Membership   Reviews
A     yes        has some   has count    0
B     yes        has some   has count    0
C     no         no         no memb.     2
D     no         has some   has count    1

Expected sort order results: A B D C

Sorry of thats confusing.

Heres what I have already tried:

SELECT * FROM (SELECT * FROM gym WHERE (city = "Queens") AND (state = "NY") GROUP BY priority ORDER BY photo, member_includes, reviews DESC) x LIMIT 0, 150

SELECT * FROM (SELECT * FROM gym WHERE (city = "Queens") AND (state = "NY") ORDER BY priority, photo, member_includes, reviews DESC) x LIMIT 0, 150

SELECT * FROM (SELECT * FROM gym WHERE (city = "Queens") AND (state = "NY") GROUP BY photo, member_includes, reviews ORDER BY priority DESC) x LIMIT 0, 150

SELECT * FROM gym WHERE (city = "Queens") AND (state = "NY") GROUP BY photo, member_includes, reviews ORDER BY priority DESC LIMIT 0, 150

SELECT * FROM gym WHERE (city = "Queens") AND (state = "NY") ORDER BY priority, photo, member_includes, reviews DESC LIMIT 0, 150

And I have tried all other kinds of combinations with and without ASC but still it does not sort properly. I dont know what I am doing wrong.

Please help!

Thanks, David

Upvotes: 0

Views: 256

Answers (2)

D Mac
D Mac

Reputation: 3809

Here you go - I think you're missing two key ideas: use ifnull to map any null values to 0 and then sort with DESC so that the zeros (from the NULLs) sort to the end.

How does this work for you?:

create table gym (
    id int primary key auto_increment not null,
    name varchar(255),
    priority int,
    photo int,
    member_includes int,
    reviews int
);

insert into gym 
(id, name, priority, photo, member_includes, reviews) values
(DEFAULT, 'A', 1, 2, 3, 0),
(DEFAULT, 'B', 1, 2, 3, 0),
(DEFAULT, 'C', NULL, 0, 3, 0),
(DEFAULT, 'D', NULL, 1, 3, 1);

select name from gym 
order by ifnull(priority,0) desc
    , ifnull(photo, 0) desc
    , ifnull(member_includes, 0) desc
    , ifnull(reviews, 0) desc ;

+------+
| name |
+------+
| A    |
| B    |
| D    |
| C    |
+------+
4 rows in set (0.00 sec)

Upvotes: 2

mvp
mvp

Reputation: 116177

I believe this is what you are looking for:

SELECT * FROM gym
WHERE city = "Queens" AND state = "NY"
ORDER BY
  ISNULL(priority), priority,
  ISNULL(photo),
  ISNULL(member_includes), member_includes,
  ISNULL(reviews), reviews DESC
LIMIT 150

Upvotes: 3

Related Questions