CODEWITHSUNDEEP
sql-servert-sqlrows
SQLnubie
SQLnubie

Reputation: 39

Arrange rows in T-SQL

How to arrange rows manually in T-SQL?

I have a table result in order like this:

but the expected result is supposed to be:

edited:

My whole query is:

select (case when s.StudentID is null then 'Unknown' else s.StudentName end) as StudentName from Period pd full join Course c on pd.TimeID = c.TimeID full join Student s on c.StudentID = s.StudentID
group by s.StudentName, s.StudentID
order by case s.StudentName
            when 'Charlie' then 1
            when 'Dave' then 2
            when 'Lisa' then 3
            when 'Mary' then 4
            when 'Unknown' then 5 
            end

but it didn't work. I think the problem root is because Unknown is from NULL value, as I wrote in that query that when StudentID is null then change "NULL" to "Unknown". Is this affecting the "stubborn" order of the result? By the way I also have tried order by s.StudentName asc but also didn't work.

Thank you.

Upvotes: 0

Views: 112

Answers (3)

Christian Phillips
Christian Phillips

Reputation: 18769

Try the following...

SELECT  os.StudentName
FROM    ( SELECT    CASE WHEN s.StudentID IS NULL THEN 'Unknown'
                         ELSE s.StudentName
                    END AS StudentName
          FROM      Period pd
                    FULL JOIN Course c ON pd.TimeID = c.TimeID
                    FULL JOIN Student s ON c.StudentID = s.StudentID
          GROUP BY  s.StudentName ,
                    s.StudentID
        ) AS os
ORDER BY os.StudentName

Edit: based on comment...

When I use this, it works fine...notice the Order By has no identifier 

declare  @tblStudent TABLE (StudentID int, StudentName varchar(30));

insert into @tblStudent values (null, '');
insert into @tblStudent values (1, 'Charlie');
insert into @tblStudent values (2, 'Dave');
insert into @tblStudent values (3, 'Lisa');
insert into @tblStudent values (4, 'Mary');

SELECT  CASE WHEN s.StudentID IS NULL THEN 'Unknown'
             ELSE s.StudentName
        END AS StudentName
FROM    @tblStudent s
GROUP BY s.StudentName ,
        s.StudentID
ORDER BY StudentName

Upvotes: 1

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

As I see your rows must be ordered alphabetically, so just add in the end of the query: ORDER BY p.StudentName. If this not help, please add whole query, so we can find out the problem.

So when I see query I can explain. You try to sort by column p.StudentName. This column contains NULL. Try to sort by StudentName without p in front. This is alias of the expression which contains Unknown.

Upvotes: 0

Ran Avnon
Ran Avnon

Reputation: 758

just put the following clause in you SQL statement:
order by p.StudentName
Sql server will order the column alphabetically.

Upvotes: 0

Related Questions