Is a Specific Order By Possible?

[MJnaeblis]

i am trying to designate a specific order in which the records come out, not ascending or descending. is this possible with a Select and Order By? or perhaps another keyword that i am unfamiliar with?
i'm working in Crystal Reports with SQL Server.

example:
SELECT LastName FROM People
WHERE
(SocSecNum = 123456783 OR
SocSecNum = 123456781 OR
SocSecNum = 123456787 OR
SocSecNum = 123456782)

but somehow order it to come out in the order i need it to print out, for instance in the order i just called it.

all i'm looking for is a way to do this in the select statement. if i need a procedure (or whatever else), a simple 'use a procedure, you baffoon' will do.

thanks
MJnaeblis

 

[jhall156]

Is this something you need to change frequently?

If not then

SELECT CASE SocSecNum
WHEN '123456783' THEN 1
WHEN '123456781' THEN 2
WHEN '123456787' THEN 3
WHEN '123456787' THEN 4
ELSE 0
END AS theorder
LastName FROM People
WHERE
(SocSecNum = 123456783 OR
SocSecNum = 123456781 OR
SocSecNum = 123456787 OR
SocSecNum = 123456782)
ORDER BY theorder
JHall

 

[MJnaeblis]

thanks for the suggestion, tho Crystal didn't seem to like that. it totally ignored my Case in SQL and gave me an error on trying to sort by theorder!

...stupid Crystal reports...

thanks tho.
MJnaeblis

 

[jhall156]

sorry, I left out the comma after theorder, does that make any difference? JHall

 

[cmmrfrds]

If Crystal will not accept your query then make the query a stored procedure (SP) and return a resultset to Crystal. It will accept a stored procedure as the data source. You could send the SP a delimited list of ssn's as a parameter and then create a tempory table with the ssn and sequence number and then join your query to the temp table and order on the sequence number and return the resultset.

 

[fluteplr]

If you are returning the data to crystal, you can specify a special sort order in Crystal.

Add the field as a group by, then go into reports change group expert options and where it asks for the sort order select 'specified order' you can then view the data and force it to whatever order you want.