Solved

# complex order by clause

Posted on 2012-08-16
865 Views
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
0
Question by:PatrickK_W

LVL 12

Expert Comment

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
``````
0

LVL 1

Author Comment

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

LVL 74

Expert Comment

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

LVL 1

Author Comment

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

LVL 74

Expert Comment

How many "user N" are there?
0

LVL 1

Author Comment

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

LVL 67

Accepted Solution

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
``````
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

LVL 1

Author Closing Comment

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

LVL 67

Expert Comment

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

LVL 1

Author Comment

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

## Featured Post

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.