complex order by clause

HI. I have a slightly weird requirement for ordering records. It’s for a questionnaire. Some questions appear once on the form, while others will be repeated multiple times, once per user added to he questionnaire. So for example, there might be four questions 1-4. Questions 1 and 4 are only on the form once, but questions 2 and 3 are repeated for every user.

If there are 2 users added to the form, the normal order would be (order by question number, user)
1
2 user 1
2 user 2
3 user 1
3 user 2
4

But I want to group all of the repeated questions together by the user (for the order by), then order them by the question number ie

1
2 user1
3 user 1
2 user 2
3 user 2
4

Any ideas?

Thanks
LVL 1
PatrickK_WAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Test SQL:
with tbl as
(select 1 as question, null as usr
union all
select 2, 'user 1'
union all
select 2, 'user 2'
union all
select 3, 'user 1'
union all
select 3, 'user 2'
union
select 4, null)
select *
  from tbl
order by
case
   when usr is null then right(space(3)+cast(question as varchar(3)), 3)
   else (select right(space(3)+cast(min(question) as varchar(3)), 3) from tbl where usr = tbl.usr)
       + usr
       + right(space(3)+cast(question as varchar(3)), 3)
 end

Open in new window

This should work for up to 99 questions. The WITH clause (ending before SELECT *) is only for generating the test "table" called "tbl".
The trick is to generate an order by criterium consisting of the lowest question value per user, plus the user name, plus the actual question value. So sort order is
  1
  2user 1  2
  2user 1  3
  2user 2  2
  2user 2  3
  4
and that works. If you want to see the order "column", just add the complete case expression to the select list.
0
 
Habib PourfardSoftware DeveloperCommented:
you can declare a variable and set the value to sort it as desired:
DECLARE @OrderByQuestion bit
SET @OrderByQuestion = 1 -- If you wanna order by Question
--SET @OrderByQuestion = 0 -- If you wanna order by User


SELECT * FROM [YourTable]
ORDER BY CASE WHEN @OrderByQuestion = 1 THEN CAST(QuestionNo AS varchar(16)) ELSE Username END

Open in new window

0
 
PatrickK_WAuthor Commented:
i need to use both concurrently.

using order by question_number, username gives
1
2 user 1
2 user 2
3 user 1
3 user 2
4

but i want to take 2 and 3 out of order like this becasue they are repeated
1
2 user 1
3 user 2

2 user 2
3 user 2

4
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
käµfm³d 👽Commented:
What happens if you flip the two columns in the ORDER BY (e.g.  username, question_number)?
0
 
PatrickK_WAuthor Commented:
in that case, 1 and 4 have a null username where the multiples of 2 and 3 have a value. so we get
1
4
2 user 1
3 user 1
2 user 2
3 user 2


i'd already tried variations like that. i have even tried soe ranking stuff, but can't get it quite right.
0
 
käµfm³d 👽Commented:
How many "user N" are there?
0
 
PatrickK_WAuthor Commented:
the number of users can be vairable per questionnaire could be 1, could be 6 or any number.

that siad, any questions that ae per user will be for EVERY user on the questionniare.

so 2 would appear 6 times if there are 6 users, likewise for 3
0
 
PatrickK_WAuthor Commented:
thats great, thanks. I have a slight problem in that i have a derived table  to get my result set that i am going to order, so the case wont work becasue you can't do a select from [derived table], but that cna be sorted by inseting into a table vairable instead of suing aderived table.

thanks
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Instead of using a table var, you can use a CTE (WITH) for the derived table. That treats it like a view, and you can use it in the ORDER BY. The same applies if you derive from the derived table.
0
 
PatrickK_WAuthor Commented:
good point..... especially as it's already in the example.... !! thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.