SQL multiple column sort not working

Question

I try to select data from a table and order rows by 2 columns. It's sorted ok by first column(likes) but for the second one not.

SELECT *
FROM content
WHERE topic='$id' AND date='$date'
ORDER BY likes DESC, cpc DESC
LIMIT 4

I thought problems is in LIMIT, i deleted it, but is not working as well. What can be the problem? Thank you

It's just a part of my table, here's just the columns i am operating with

Please edit your question with sample data that is not being sorted as you expect it. — Gordon Linoff
– Gordon Linoff, Commented Mar 21, 2014 at 19:25
Your data shows no issues. It first sorted by likes descending, since there are no duplicates the CPC sort didn't matter. Perhaps you need to do CPC first then likes? Or you need to understand how sorting works... which is in the order listed. — xQbert
– xQbert, Commented Mar 21, 2014 at 19:29
Yes, is sorted descending by likes but for cpc ís sorted ascending. Don't now what can be the reason. — Valeriu Mazare
– Valeriu Mazare, Commented Mar 21, 2014 at 19:31

Pellared · Accepted Answer · 2014-03-21 19:43:22Z

4

It is OK! It first sorts by likes and afterwards if some data would have the same value of values then it would sort by cpc. But your likes are unique so there is no difference between:

SELECT *
FROM content
WHERE topic='$id' AND date='$date'
ORDER BY likes DESC, cpc DESC

and

SELECT *
FROM content
WHERE topic='$id' AND date='$date'
ORDER BY likes DESC

Probably you want:

SELECT * 
FROM (SELECT *
  FROM content
  WHERE topic='$id' AND date='$date'
  ORDER BY likes DESC
  LIMIT 4) a
ORDER BY a.cpc DESC

edited Mar 21, 2014 at 19:43

answered Mar 21, 2014 at 19:30

Pellared

1,2922 gold badges14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Valeriu Mazare Over a year ago

Ook, my fault. I understood now. But if i want this: to select 4 entries ordered by likes DESC, and then, sort these 4 by cpc ? how can i do that? thank you.

Pellared Over a year ago

@ValeriuMazare Check now please

user2858650 · Accepted Answer · 2014-03-21 19:38:05Z

It's vital to remember that all of the data in your tables are related. So field values in your CPC column directly tie to the field values in your likes column.

The results of each column do not act independent of each other (at least not the way you have written this query).

So with that concept in mind, this data is sorted correctly. Your Likes column is sorted and then the CPC is sorted based on the static Likes sort. If you had two lines of data with 20 likes and the CPC was 2 and 20, respectively, then you would see those values sorted in order based on your

 Order by cpc desc

line. But since you only have one CPC value for your Likes line of 20, that will be the value to appear. Take the same example for the next line, and the one after that and you can see that the values do in fact appear in order.

Collectives™ on Stack Overflow

SQL multiple column sort not working

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related