ORDER BY Case

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I'm trying to use CASE to evaulate a @sortType variable and let ORDER BY
sort by either lastname, firstname or social security. my code gives error
if I use "u.userLast, u.userFirst", but works fine if I just use "userLast".

What's wrong with using 2 fields to sort by following THEN?

CODE ********
declare @sortType int

set @sortType = '1'

BEGIN

SELECT u.userID, u.userLast, u.userFirst, u.userSS FROM t_users AS u

ORDER BY

CASE

WHEN @sortType = 1 THEN u.userLast, u.userFirst

WHEN @sortType = 2 THEN u.userSS

END

END
 
scott wrote in message said:
I'm trying to use CASE to evaulate a @sortType variable and let ORDER
BY sort by either lastname, firstname or social security. my code
gives error if I use "u.userLast, u.userFirst", but works fine if I
just use "userLast".

What's wrong with using 2 fields to sort by following THEN?

CODE ********
declare @sortType int

set @sortType = '1'

BEGIN

SELECT u.userID, u.userLast, u.userFirst, u.userSS FROM t_users AS u

ORDER BY

CASE

WHEN @sortType = 1 THEN u.userLast, u.userFirst

WHEN @sortType = 2 THEN u.userSS

END

END
Click to expand...

I think this is perhaps more a T-SQL/SQL-server question than Access.
This is perhaps not very efficient, but try something like this

WHEN @sortType = 1 THEN u.userLast + ' ' + u.userFirst
 
Back
Top