Specific question about ORDER BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Specific question about ORDER BY

Author

Topic

Hostile72
Starting Member

2 Posts

Posted - 2010-09-22 : 15:22:44

I'm trying to take somebody else's code who used to work my job but is gone and add a sort order. Right now it's ascending by date. I need it to descend.

$Query='select * from '.$Category.' where isAvailable<>""';

Normally this would be done by adding this

SELECT * FROM table_name
ORDER BY Date DESC

I tried this but it didn't work.

$Query='select * from '.$Category.' ORDER BY Date DESC where isAvailable<>""';

I know it's something simple as I'm not the best coder around. Any help will do.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-22 : 15:26:44

You need to put the ORDER BY clause after the WHERE clause. I'm not familar with that syntax but something like:

$Query='select * from '.$Category.' where isAvailable<>""  ORDER BY Date DESC';

Hostile72
Starting Member

2 Posts

Posted - 2010-09-22 : 15:42:36

quote:
Originally posted by Lamprey

You need to put the ORDER BY clause after the WHERE clause. I'm not familar with that syntax but something like:
$Query='select * from '.$Category.' where isAvailable<>"" ORDER BY Date DESC';

That looks like it's doing the job except I now realized the original coder set up the date field as a TEXT and not DATE. Even if I change it to DATE, it still sorts really funny. Oh well, thanks for the reply.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-22 : 15:46:17

You can order by convert(datetime,date) desc

Jim

Everyday I learn something that somebody else already knew

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 04:40:55

yes you *could*

Or you could do the proper thing and change the table so that dates are stored as dates. It would be a lot more work (you'd have to regression test) but it might be worth it in the long run.

Doing a blind CONVERT(DATETIME, xxx) might well fail (with an error) if there are non valid dates in the table.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources