x

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 |
 +--------------+-------------------+---------------+-------------------+----------+------------+---------+---------------+
more ▼

asked Aug 10 at 03:28 AM in Default

avatar image

vagallasuresh
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Aug 10 at 08:01 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

Thank you, Thomas, This data I', getting already. Looking for rest. Hope you will help

Aug 10 at 08:37 AM vagallasuresh
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1090
x247
x13

asked: Aug 10 at 03:28 AM

Seen: 28 times

Last Updated: Aug 10 at 12:41 PM

Copyright 2017 Redgate Software. Privacy Policy