Problem with ORDER BY statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with ORDER BY statement

Author

Topic

savvy
Starting Member

48 Posts

Posted - 2005-09-28 : 11:10:20

I have list of sort options say Relevance, Date Posted, Salary and Job Location. I want to ORDER the search results with the options given above. Relevance, Salary and JobLocation in ascending order and Date Posted in Descending order. I tried adding ORDER BY to the below statement using case but its not working out. And ORDER BY doesn't accept variables as well. Can I use ORDER BY or Is their any other way handle this.
Thanks in Advance.

SELECT J.* FROM JobDetails AS J
JOIN
(
SELECT DISTINCT J1.JobID FROM JobDetails AS J1
inner join dbo.Split1(@list, ' ') AS S
ON J1.JobPosition LIKE '%' + S.Data + '%') J1 ON J1.JobID = J.JobID

Savvy

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-28 : 11:14:30

Hi

Case should work in the orderby clause..but u can not directly add the variable to it.

for Eg

Declare @OrderBy Int
Set @OrderBy = 1

SELECT J.* FROM JobDetails AS J
JOIN
(
SELECT DISTINCT J1.JobID FROM JobDetails AS J1
inner join dbo.Split1(@list, ' ') AS S
ON J1.JobPosition LIKE '%' + S.Data + '%' J1 ON J1.JobID = J.JobID
Order By
Case @OrderBy When 1 then Relevance
Else Date_Posted End

i hope this made any sense to u ..

Complicated things can be done by simple thinking

savvy
Starting Member

48 Posts

Posted - 2005-09-28 : 11:21:40

Thanks for ur reply
Its working for couple of things for e.g. Date_Posted and Salary but when i use the case statement for JobPosition and Region then it says
Syntax error converting datetime from character string.
And moreover its not letting me do Ascending or Descending orders

Savvy

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-09-28 : 11:28:16

Hi..
Can you post what are u trying to do .. ???

I mean same query and sample data.

Complicated things can be done by simple thinking

savvy
Starting Member

48 Posts

Posted - 2005-09-29 : 04:25:53

I got it finally. I used multiple case statements and it worked.
I am giving my code below . Thank u very much for all your help Chiragkhabaria.

SELECT J.* FROM JobDetails AS J
JOIN
(
SELECT DISTINCT J1.JobID FROM JobDetails AS J1
inner join dbo.Split1(@list, ' ') AS S
ON J1.JobPosition LIKE '%' + S.Data + '%') J1 ON J1.JobID = J.JobID
ORDER BY
CASE WHEN @SortBy = 'Relevance' THEN Relevance END,
CASE WHEN @SortBy = 'Salary' THEN Salary END,
CASE WHEN @SortBy = 'JobLocation' THEN JobLocation END,
CASE WHEN @SortBy = 'DatePosted' THEN DatePosted END DESC

Savvy

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-29 : 10:46:06

[code]
SELECT J.* FROM JobDetails AS J
JOIN
(
SELECT DISTINCT J1.JobID FROM JobDetails AS J1
inner join dbo.Split1(@list, ' ') AS S
ON J1.JobPosition LIKE '%' + S.Data + '%') J1 ON J1.JobID = J.JobID
ORDER BY
CASE WHEN @SortBy = 'Relevance' THEN Relevance
WHEN @SortBy = 'Salary' THEN Salary
WHEN @SortBy = 'JobLocation' THEN JobLocation
WHEN @SortBy = 'DatePosted' THEN DatePosted END DESC
[/code]

This should also work. Just use one case.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources