?
Solved

complex order by clause

Posted on 2012-08-16
10
Medium Priority
?
869 Views
Last Modified: 2012-08-17
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
Comment
Question by:PatrickK_W
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38303751
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
 
LVL 1

Author Comment

by:PatrickK_W
ID: 38303756
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 75

Expert Comment

by:käµfm³d 👽
ID: 38303785
What happens if you flip the two columns in the ORDER BY (e.g.  username, question_number)?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:PatrickK_W
ID: 38303788
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 75

Expert Comment

by:käµfm³d 👽
ID: 38303791
How many "user N" are there?
0
 
LVL 1

Author Comment

by:PatrickK_W
ID: 38303797
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 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 38303829
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
 
LVL 1

Author Closing Comment

by:PatrickK_W
ID: 38303872
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 71

Expert Comment

by:Qlemo
ID: 38303890
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

by:PatrickK_W
ID: 38303896
good point..... especially as it's already in the example.... !! thanks!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question