Difficult SQL ORDER BY Problem

d60eba

Hello everyone,

I have been pulling my hair out in this one from a while, so I really hope we have some ORDER BY experts in the house 🙂

I have a table with news items. Each news item has (amongst others) a date_from field, a sticky field, and an active field. When I extract my news items from the database I would like to be able to sort them as follows:

Those where sticky = y, sorted by date_from
Those where active = y, sorted by date_from

Now, here's the annoying part. The following:

ORDER BY sticky DESC, active DESC, date_from DESC

Will sort by sticky (by what criterion I don't know), and then sorted by active by date_from (as I would like).

So,... does anyone know how I can get that those first sticky entires sorted by date_from aswell?

I hope this makes sense, if not please ask for further clarification.

Many thanks,

Leon Chevalier

adrian_quah

hey Leon,

The ORDER BY sticky DESC, active DESC, date_from DESC
means it'll sort by all three 'sticky', 'active' and 'date_from'...

e.g.

sticky | active | date_from
1 1 1
1 2 1
1 2 2
1 2 3
2 1 1

Something like this...If u need SQL statements that can chage depending on the values of the variables $sticky or $active, i suggested u use PHP to do it as in: -

if ($sticky=='y')
{
mysql_query("......");
}
else
{
mysql_query("____");
}

Hope it gives u some idea 😉

adrian_quah

I'd like to give a better database contents display of ur table when
ORDER BY sticky DESC, active DESC, date_from DESC is used: -

sticky | active | date_from
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
2 1 1

d60eba

Hi adrian,

Many thanks for your reply - really appreciate it. I like the suggestion about using PHP to use a different query depending on a variable. However, in this case I would like to just list all the entries in the table. So it's not what's displayed that's important, it's the order in which they are displayed.

Your database display table is interesting, and I'm not quite sure I understand it. Could you explain what it means, and how you worked it out? Also, any advanced info you have on how the ORDER BY works would be great. The Manual only really mentions it briefly.

To get back to my problem, I worked out why the sticky wasn't sorting by date_from as I expected - well, it was, I just had a 2002 instead of a 2003 and hadn't noticed. Ahem, sorry. So it would seem that:

SELECT id_news, date_from, sticky, active
FROM pi_news
ORDER BY sticky DESC , active DESC , date_from DESC

Does result in:

id_news date_from sticky active

2 2003-01-25 00:00:00 y y
1 2003-01-23 13:00:00 y y
4 2003-01-23 00:00:00 n y
5 2003-01-18 00:00:00 n y
3 2003-01-10 00:00:00 n y
6 2003-01-19 13:00:00 n n

Which is exactly what I wanted - sorted by sticky (which is then sorted by date_from), then sorted by active (which is then sorted by date_from).

So, I suppose I never really had a question that could be answered properly in the first place. Apologies 🙂

However, any info on ORDER BY (links, tutorials etc) would be greatly appreciated as I still don't understand how it works and am basically just using trial and error.

Many thanks,

Leon Chevalier

Wien

Originally posted by d60eba
ORDER BY sticky DESC , active DESC , date_from DESC

Which is exactly what I wanted - sorted by sticky (which is then sorted by date_from), then sorted by active (which is then sorted by date_from).

Not really !
it's sorted by sticky
then for two equals sticky it's sorted by active
and finally for two equals sticky and active it's sorted by date_from
have a look there or here

d60eba

Hi Wien,

Thanks very much for your reply and for the links.

Regards,

Leon Chevalier

Lars_Berg

order by sticky desc,
case when sticky = 'Y' then 
'N' else active end, date_from

d60eba

Hi Lars,

You are the man, that is an impressive order by! MySQL just keeps on getting more and more impressive in terms of what you can achieve inside the query itself.

However, I wish I understood what the case when clause does there. If the sticky row contains a 'Y' then... what happens? What is 'N' ? Again, I found the MySQL Manual to be rather unhelpful in explaining.

Now, I have an extension to this question! (I'm sure you'll be glad to hear 🙂 ) Something I would love to be able to do would be to print out all the sticky elements (in date order), and then print out all the other elements randomly. However, this:

ORDER BY sticky
desc , rand( ) , date_from

Will sort by sticky (in order of the primary key it seems), and then sorts the rest randomly. It seems to me I might be able to use your clever logic to get the sticky to sort by date, and then the rest randomly. But how? Anyone got any ideas?

Many thanks in advance,

Leon Chevalier

Lars_Berg

You shouldn't get overly excited on the behalf of Mysql for this. It is plain SQL and supported by most DBMSs.

case when sticky = 'Y' then 'N' else active end

means that if the value of sticky = 'Y' then the result of the expression is 'N' else it will be the value for active.

I.e. the sort will be done as

Sticky | case expr | date_from

Y........|N................| 2003-01-02
Y........|N................| 2003-01-03
N.......|<active>.....| 2002-12-31
N.......|<active>.....| 2003-01-01

The 'N' is an arbitrary value.

Your extension, maybe something like

order by sticky desc,
case when sticky = 'Y' then -1 else rand() end, date_from