Another unusual query

Is it possible to write a query in 1 go where you would order the results firstly where x = "this value" and then order by y?

I have a table where the field "status" can be pending or complete or some other varieties and I want to order by firstly status=pending and then by deadline.

 

Yes it is possible

select * from somewhere
order by status, deadline

Code (sql):

 

Thanks dct

Thats all very well but I have statuses that come before and after "pending" so I cant use ORDER BY status or ORDER BY status DESC.


Any other ideas?

 

Sure you can just add them like the columns you select:

... order by status desc, deadline asc

or something like that. Desc = descending, asc= ascending.

 

lol you replied before I got a chance to stick in my reply Perrow

 

For some reason I just typed too slow. When I started writing DCT hadn't answered yet.

 

lol

I think I need some kind of query that says

SELECT * FROM something
ORDER BY status='pending', deadline DESC

 

If you have an id field there is a hacky way you could do it, say pending has a status_id of 10 do the following

select * from something
order by 
abs(status_id - 10), deadline DESC

Code (sql):

 

Hmmm, I suppose if you're going to go to that trouble you might as well make an extra field called pending and set it to 1 if status is pending. That way you can just order the list by the pending field.

Thanks for that little tip though dct. I've never seen that before.

 

I often add an order_by field for just that purpose, though sometimes it's good to hack .

 

I agree... hacking is fun!!! errrrr.....

 

This is why your status id's should always be prime numbers

Searched the internet (called googling I'll teach you some day), found this:

SELECT * FROM tickets
ORDER BY FIELD(priority, 'High', 'Normal', 'Low', 'The Abyss');

Code (sql):

Should help you.

Source: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

 

Thanks Perrow not seen that before (from an MSSQL background).

err what?

 

So they get unique values when you do your little calculation, you don't want two status codes to wind up with the same value, now do you

Too much math in my head, I know

 

Just so you'll know, that post was my 1000th

 

GENIUS!!!! That has to go down as the best 1000th post ever on DP!


Thanks Perrow!

 

That was actually number 999, 1000 was my explanation of the prime numbers rant, wich may not be the best ever 1000th post. But I'll settle for the best ever 999th post

 

Shhhh! I was trying to get you some fame there buddy lol.