asked on

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

Habib Pourfard

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

PatrickK_W

ASKER

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

kaufmed

What happens if you flip the two columns in the ORDER BY (e.g. username, question_number)?

PatrickK_W

ASKER

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.

kaufmed

How many "user N" are there?

PatrickK_W

ASKER

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

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatrickK_W

ASKER

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

Qlemo

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.

PatrickK_W

ASKER

good point..... especially as it's already in the example.... !! thanks!