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]]

Re: order by where....

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" <joey@infosmith s.net> wrote in message
news:OC%23aOXVh EHA.704@TK2MSFT NGP09.phx.gbl.. .[color=blue]
> 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![/color]

[Dave Anderson]

Re: order by where....

Joey Martin wrote:[color=blue]
>
> 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.[/color]

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

ORDER BY CHARINDEX(Featu red,'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("Virg inia",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]]

Re: order by where....

Joey Martin wrote:[color=blue]
> 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.
>[/color]
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]]

Re: order by where....

Dave Anderson wrote:[color=blue]
> Joey Martin wrote:[color=green]
>>
>> 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.[/color]
>
> This will work if [Featured] is CHAR(1):
>
> ORDER BY CHARINDEX(Featu red,'Y') DESC
>[/color]
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]

Re: order by where....

Bob Barrows [MVP] wrote:[color=blue]
> Dave Anderson wrote:[color=green]
>> Joey Martin wrote:[color=darkred]
>>>
>>> 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.[/color]
>>
>> This will work if [Featured] is CHAR(1):
>>
>> ORDER BY CHARINDEX(Featu red,'Y') DESC
>>[/color]
> Only if he is using SQL Server ...[/color]

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]]

Re: order by where....

Dave Anderson wrote:[color=blue]
> Bob Barrows [MVP] wrote:[color=green]
>> Dave Anderson wrote:[color=darkred]
>>> 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(Featu red,'Y') DESC
>>>[/color]
>> Only if he is using SQL Server ...[/color]
>
> Or Sybase.
>
> But I should think instr() would do the job in Oracle or MySQL...[/color]

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.