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.

 

[Bob Barrows [MVP]]

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.

 

[Dave Anderson]

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

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

 

[Dave Anderson]

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

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