order by where....

[Joey Martin]

Pleas ehelp me with this.

I am performing a basic sql query.

select * from tablename order by featured asc, id asc

BUT...I want all those listings with FEATURED as a Y to show up first.
All other listings should show up afterwards.

How do I do this? I may be thinking too hard about it.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Curt_C [MVP]]

What's special about the "Y" ones, I mean you might be able to use a third
criteria in the order by clause if there is...
Otherwise you'll have to do it manually I think.

Curt

"Joey Martin" <jo**@infosmiths.net> wrote in message
news:OC***************@TK2MSFTNGP09.phx.gbl...

Pleas ehelp me with this.

I am performing a basic sql query.

select * from tablename order by featured asc, id asc

BUT...I want all those listings with FEATURED as a Y to show up first.
All other listings should show up afterwards.

How do I do this? I may be thinking too hard about it.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dave Anderson]

Joey Martin wrote:

select * from tablename order by featured asc, id asc

BUT...I want all those listings with FEATURED as a Y to show up first.
All other listings should show up afterwards.

This will work if [Featured] is CHAR(1):

ORDER BY CHARINDEX(Featured,'Y') DESC
In general, you can use this technique on any CHAR/VARCHAR type if you
muliply the crossed CHARINDEX values:

SELECT * FROM States
ORDER BY
CHARINDEX(Name,"Virginia") * CHARINDEX("Virginia",Name) DESC,
Name ASC
The multiplication prevents false positives such as "West Virginia".

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Bob Barrows [MVP]]

Joey Martin wrote:

Pleas ehelp me with this.

I am performing a basic sql query.

select * from tablename order by featured asc, id asc

BUT...I want all those listings with FEATURED as a Y to show up first.
All other listings should show up afterwards.

How do I do this? I may be thinking too hard about it.

What database? Type and version please.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Dave Anderson wrote:

Joey Martin wrote:

select * from tablename order by featured asc, id asc

BUT...I want all those listings with FEATURED as a Y to show up
first. All other listings should show up afterwards.

This will work if [Featured] is CHAR(1):

ORDER BY CHARINDEX(Featured,'Y') DESC

Only if he is using SQL Server ...

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Dave Anderson]

Bob Barrows [MVP] wrote:

Dave Anderson wrote:

Joey Martin wrote:

select * from tablename order by featured asc, id asc

BUT...I want all those listings with FEATURED as a Y to show up
first. All other listings should show up afterwards.

This will work if [Featured] is CHAR(1):

ORDER BY CHARINDEX(Featured,'Y') DESC

Only if he is using SQL Server ...

Or Sybase.

But I should think instr() would do the job in Oracle or MySQL...


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Bob Barrows [MVP]]

Dave Anderson wrote:

Bob Barrows [MVP] wrote:

Dave Anderson wrote:

Joey Martin wrote:

select * from tablename order by featured asc, id asc

BUT...I want all those listings with FEATURED as a Y to show up
first. All other listings should show up afterwards.

This will work if [Featured] is CHAR(1):

ORDER BY CHARINDEX(Featured,'Y') DESC

Only if he is using SQL Server ...

Or Sybase.

But I should think instr() would do the job in Oracle or MySQL...

Maybe. But my point is that the answer depends on the database being used,
which information was not provided by the OP.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.