question

vagallasuresh avatar image
vagallasuresh asked

How to Order multiple Rows in SQL Server

declare @result table (FirstFieldID int, FirstFieldIDName varchar(100), SecondFieldID int, SecondFieldName varchar(100),ObjectID int, ObjectName varchar(100), SubSort int ,TotalStudents int) insert into @result select 1000003, 'Gender', 1000125, 'Female', -1 ,'-1', -4, 3 union select 1000003, 'Gender', 1000125, 'Female', 220 ,'Grade 12', -3, 2 union select 1000003, 'Gender', 1000125, 'Female', 200 ,'Grade 10', -3, 1 union select 1000003, 'Gender', 1000126, 'Male', -1 ,'-1', -4, 5 union select 1000003, 'Gender', 1000126, 'Male', 210 ,'Grade 11', -3, 3 union select 1000003, 'Gender', 1000126, 'Male', 220 ,'Grade 12', -3, 1 union select 1000003, 'Gender', 1000126, 'Male', 140 ,'Grade 4', -3, 1 union select 1000021, 'Title I Indicator', 1000380, 'Title I Indicator', -1, '-1', -4, 7 union select 1000021, 'Title I Indicator', 1000380, 'Title I Indicator', 210 ,'Grade 11', -3, 3 union select 1000021, 'Title I Indicator', 1000380, 'Title I Indicator', 220 ,'Grade 12', -3, 3 union select 1000021, 'Title I Indicator', 1000380, 'Title I Indicator', 200 ,'Grade 10', -3, 1 union select 1000010, 'Birth Country', 1000285, 'US', -1 ,'-1', -4, 4 union select 1000010, 'Birth Country', 1000285, 'US', 210 ,'Grade 11', -3, 2 union select 1000010, 'Birth Country', 1000285, 'US', 220 ,'Grade 12', -3, 2 select * from @result +--------------+-------------------+---------------+-------------------+----------+------------+---------+---------------+ | FirstFieldID | FirstFieldIDName | SecondFieldID | SecondFieldName | ObjectID | ObjectName | SubSort | TotalStudents | +--------------+-------------------+---------------+-------------------+----------+------------+---------+---------------+ | 1000003 | Gender | 1000125 | Female | -1 | -1 | -4 | 3 | | 1000003 | Gender | 1000125 | Female | 220 | Grade 12 | -3 | 2 | | 1000003 | Gender | 1000125 | Female | 200 | Grade 10 | -3 | 1 | | 1000003 | Gender | 1000126 | Male | -1 | -1 | -4 | 5 | | 1000003 | Gender | 1000126 | Male | 210 | Grade 11 | -3 | 3 | | 1000003 | Gender | 1000126 | Male | 220 | Grade 12 | -3 | 1 | | 1000003 | Gender | 1000126 | Male | 140 | Grade 4 | -3 | 1 | | 1000021 | Title I Indicator | 1000380 | Title I Indicator | -1 | -1 | -4 | 7 | | 1000021 | Title I Indicator | 1000380 | Title I Indicator | 210 | Grade 11 | -3 | 3 | | 1000021 | Title I Indicator | 1000380 | Title I Indicator | 220 | Grade 12 | -3 | 3 | | 1000021 | Title I Indicator | 1000380 | Title I Indicator | 200 | Grade 10 | -3 | 1 | | 1000010 | Birth Country | 1000285 | US | -1 | -1 | -4 | 4 | | 1000010 | Birth Country | 1000285 | US | 210 | Grade 11 | -3 | 2 | | 1000010 | Birth Country | 1000285 | US | 220 | Grade 12 | -3 | 2 | +--------------+-------------------+---------------+-------------------+----------+------------+---------+---------------+ At present my data would be like above. When ObjectID and ObjectName are -1 then the TotalStudents will be in descending order with in the group. Otherwise ObjectName is Ascending order. Expecting the data like below. +--------------+-------------------+---------------+-------------------+----------+------------+---------+---------------+ | FirstFieldID | FirstFieldIDName | SecondFieldID | SecondFieldName | ObjectID | ObjectName | SubSort | TotalStudents | +--------------+-------------------+---------------+-------------------+----------+------------+---------+---------------+ | 1000021 | Title I Indicator | 1000380 | Title I Indicator | -1 | -1 | -4 | 7 | | 1000021 | Title I Indicator | 1000380 | Title I Indicator | 200 | Grade 10 | -3 | 1 | | 1000021 | Title I Indicator | 1000380 | Title I Indicator | 210 | Grade 11 | -3 | 3 | | 1000021 | Title I Indicator | 1000380 | Title I Indicator | 220 | Grade 12 | -3 | 3 | | 1000003 | Gender | 1000126 | Male | -1 | -1 | -4 | 5 | | 1000003 | Gender | 1000126 | Male | 140 | Grade 4 | -3 | 1 | | 1000003 | Gender | 1000126 | Male | 220 | Grade 12 | -3 | 1 | | 1000003 | Gender | 1000126 | Male | 210 | Grade 11 | -3 | 3 | | 1000010 | Birth Country | 1000285 | US | -1 | -1 | -4 | 4 | | 1000010 | Birth Country | 1000285 | US | 210 | Grade 11 | -3 | 2 | | 1000010 | Birth Country | 1000285 | US | 220 | Grade 12 | -3 | 2 | | 1000003 | Gender | 1000125 | Female | -1 | -1 | -4 | 3 | | 1000003 | Gender | 1000125 | Female | 200 | Grade 10 | -3 | 1 | | 1000003 | Gender | 1000125 | Female | 220 | Grade 12 | -3 | 2 | +--------------+-------------------+---------------+-------------------+----------+------------+---------+---------------+
t-sqlsql serverms sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
You can put more complex logic into an ORDER BY clause, such as: SELECT ... ORDER BY SecondFieldID, CASE WHEN ObjectID = -1 AND ObjectName = -1 THEN 0 ELSE 1 END I'm not sure I follow the logic of the rest of your ordering, but at least this will get you part way there, and you should be able to figure out the rest. --edit-- OK, having read your comment, I'm still not sure that I get your exact requirement, but here's an idea. Assuming ObjectName where objectid = -1 in each firstnameid,secondnameid group gives the sortorder (-1 or +1), then you could do something like: WITH SortOrderAdded AS ( SELECT * , ( SELECT ObjectID FROM @result r2 WHERE r.FirstFieldID = r2.FirstFieldID AND r.SecondFieldID = r2.SecondFieldID AND r2.ObjectName = '-1' ) AS SortOrder FROM @result r ) SELECT * FROM SortOrderAdded ORDER BY SecondFieldID , FirstFieldID , CASE WHEN ObjectName = '-1' THEN -9999 ELSE TotalStudents * SortOrder END ; So here we get data sorted by SecondNameID, FirstNameID, and then within each of those groups sorted by number of students with the ObjectName='-1' record at the top, and, if objectID is 1 then it's in ascending order, and if objectID is -1 then it's in descending order. Does this help you get closer? Obviously, I've not done anything about sorting the blocks of data in the order you want...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vagallasuresh avatar image vagallasuresh commented ·
Thank you, Thomas, This data I', getting already. Looking for rest. Hope you will help
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.