order by criteria - SQL Server Forums

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

order by criteria

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-07 : 08:26:43

jiang writes "Apologies in advance for my inexperience.

I have a SQL table to hold my product information:
prods(prodnum(char(10), prodname(char20), quantity(int))

The values in prodname column are like:
ABCDEF
ADCDEF
BCDEFG
CDEFGH

For those products that sold out, I made a mark in the front of prodname, like *ABCDEF

Then in my query, I want to sort the product name in alphabetic order, in addition, I also want to put prodname start with * at the end of the result list, like:

ADCDEF
BCDEFG
CDEFGH
*ABCDEF

I tried to use:
select prodname from prods order by prodname

this query shows *ABCDEF is on the top of the result, then I tried:

select prodname from prods order by charindex('*', namecode)

this query does put *ABCDEF at the bottom, but other records are not in alphabetic order.

Could you please help me? Many many thanks!
Jiang"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-07 : 08:34:06

Why not use a another column for the flag

order by case when left(prodname,1) = * then 2 else 1 end, prodname

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-07 : 08:47:16

or

order by case when prodname like '*%' then 2 else 1 end, prodname

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 12:29:25

quote:
For those products that sold out, I made a mark in the front of prodname, like *ABCDEF

You should create a new column in your table to represent this information.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources