sql server programming: Order by col = value -- sql server programming

Order by col = value

Ray Costanzo
1/14/2007 10:19:24 PM

sql server programming:

Hi group,

Is it possible to put a statement that will be evaluated in an ORDER BY
clause? The situation is such that I want to retrieve a record that has an
ID that I pass in, but if that ID does not exist, I want one other specific
record to be returned, say with an ID of 50. So, for example:

CREATE PROC test (@recordID int, @something varchar(50) output) AS
SELEECT
@something = someColumn
FROM
someTable
WHERE
recordID = @recordID OR recordID = 50
ORDER BY
recordID = @recordID

If this were to work, I believe that it would return the record with a
recordID of @recordID, and if it didn't exist, it would give me the record
with recordID of 50. I know that I can do "IF EXISTS (SELECT * FROM
someTable WHERE recordID = @recordID)..." I'm wondering if I can do it in a
way similar to what I described, though. (Or perhaps I'd have to DESC my
order by since true (1) is > false (0), but that's fine.)

Thank you,

Ray at work

Re: Order by col = value

Roy Harvey
1/14/2007 10:46:58 PM

As written the row with a value of 50 would always be returned, and
the row with @recordID would also be returned if it exists. I believe
this would return only one:

WHERE
recordID = COALESCE((select recordID from someTable
where recordID = @recordID), 50)

As for the ORDER BY, it makes no sense to try to perform an assignment
in an ORDER BY clause. It also makes no sense to have an ORDER BY
clause when the SELECT list only has a @variable assignment. And it
usually does not make sense to ORDER BY something that does not appear
in the SELECT list.

Roy Harvey
Beacon Falls, CT

On Sun, 14 Jan 2007 22:19:24 -0500, "Ray Costanzo" <my first name at
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.