ORDER BY probs

[Smifffy]

okie me an a m8 are trying to get this query to work:

SELECT * FROM table WHERE id = 1 ORDER BY pinned, date DESC LIMIT 0,10

basicly what i want it to do is get the first 10 rows first orded by pinned (so all the pinned items come first) then order by date (so its all ordered by date)

date is a timestamp and pinned is a single int (1 = pinned 0 = not pinned)

e.g. (i know the timestamps wont work but you get what i mean)

this table set

pinned = 1 - date = 12345  

pinned = 0 - date = 75493 
pinned = 0 - date = 13252  

pinned = 1 - date = 23523 
pinned = 0 - date = 69935 
pinned = 1 - date = 73588
pinned = 0 - date = 57693
pinned = 0 - date = 58475
pinned = 0 - date = 49034
pinned = 0 - date = 38709
pinned = 0 - date = 18735

will look like:

pinned = 1 - date = 73588 
pinned = 1 - date = 23523 
pinned = 1 - date = 12345
pinned = 0 - date = 75493
pinned = 0 - date = 69935
pinned = 0 - date = 57693 
pinned = 0 - date = 13252
pinned = 0 - date = 58475
pinned = 0 - date = 49034
pinned = 0 - date = 38709

left off - 
pinned = 0 - date = 18735

so basicly:
all the pinned come first - then tey are orderd by date

then the rest of the rows to fill the rest of the 10 emty space orderd by date

[Sxooter]

Originally posted by Smifffy
okie me an a m8 are trying to get this query to work:

SELECT * FROM table WHERE id = 1 ORDER BY pinned, date DESC LIMIT 0,10

[/B]

You probably want this:

SELECT * FROM table WHERE id = 1 ORDER BY pinned DESC, date DESC LIMIT 0,10

Note the extra DESC in there.

[Smifffy]

thankyou sxooter

me an my m8 were struggling for an hour on that so we cant say thx enough

[sinatosk]

yup big thx's 🙂

I never knew you could do that in SQL :p

[Sxooter]

That's one of those really cool / really bothersome things in SQL. When you need it, it's great to know about, when you don't know about it, it bites you in the ass. 🙂

[sinatosk]

Originally posted by Sxooter
That's one of those really cool / really bothersome things in SQL. When you need it, it's great to know about, when you don't know about it, it bites you in the ass. 🙂

now I know why my ass was annoying me 😉

[Smifffy]

LOL 😛

my ass hurts too - maybe cos ive been sat down for the past 3 hours coding :\