CODEWITHSUNDEEP
sqlsql-serversql-order-by
Knells
Knells

Reputation: 837

SQL 'Order By' Clause Appears to Work, but Returns Unsorted Data

I have this table valued function in SQL Server 2014. The important part is the 'ORDER BY' at the end, which seems to be not doing anything. The end result is the same if I take it out or remove the DESC. Why would this be? I'd generally expect it to order by most recent -> least recent when used this way.

BEGIN
    INSERT INTO @result (Id, Name, Description, TypeID, Date)
        select b.ID, b.Name, b.Description, b.TypeID, max(Date) from BTable b
        inner join ATTable at on b.atID = at.ID
        inner join STATTable stat on at.ID= stat.atID
        inner join CCTable cc on stat.stID= cc.stID
        inner join STTAble st on cc.stID= st.ID
        inner join TSTable ts on ts.cID = @cID and ts.bID = b.ID
            where b.Active = 1 and at.Active = 1 and stat.Active = 1 and cc.Active = 1 and st.Type = 1 AND 
                cc.cID = @cId and (b.cID IS NULL OR b.cID = @cId) and b.aID = (Select c.aID from CTable c where c.ID = @cId)
        GROUP BY b.ID, b.Name, b.Description, b.atID
        ORDER BY Max(Date) DESC
RETURN

When using this function I get something like:

Id      |Name                  | Date
----------------------------------------------------------------------------
32      |Mary       | NULL | 1 |    2015-06-04 00:00:00.000
68      |Joyce      | NULL | 1 |    2015-02-25 00:00:00.000
1069    |James      | NULL | 1 |    2014-03-31 00:00:00.000
1079    |Nwabudike  | NULL | 1 |    2015-06-04 00:00:00.000
1143    |Hala       | NULL | 1 |    2015-03-17 00:00:00.000

Which is quite obviously not sorted by date. Why is this, and how can I get it to order correctly?

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You can't.

The order of the result set is determined only by the order by in the outermost query.

So, forget the order by in the function. And then call the function as:

select t.*
from dbo.tblfunc() t
order by t.date_received desc;

EDIT:

To be honest, if @result were a "real" table with a clustered, identity, primary key and you inserted the rows using order by, then there is quite a good chance that you'd get them back in order. This is true for smaller tables (because the table is scanned by the clustered index). I'm not sure if it is always true in a parallel environment.

Upvotes: 2

Related Questions