CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Joshua Enfield
Joshua Enfield

Reputation: 18288

SQL - OVER and ORDERBY

I don't understand OVER very well, and I wanted to clarify what this does what I think it does.

If I have a provided Sort type, record Status, and a Meeting Date:

I would like to sort by MeetingDate by default. I would like to sort by Status when sort type is 2, and then by Meeting Date.

Does the below code do this? My testing indicates it does, but I could be missing something.

SELECT Foo.*,
    ROW_NUMBER() OVER (
        ORDER BY 
            CASE @SortType WHEN 2 THEN Foo.FK_Status END ASC,
            Foo.MeetingDate DESC
    ) AS RowOrder

More specifically, if @SortType is 1 does is it appropriate to believe the code breaks down as:

SELECT Foo.*,
    ROW_NUMBER() OVER (
        ORDER BY 
            ASC,
            Foo.MeetingDate DESC
    ) AS RowOrder

Which I think might break down to just:

SELECT Foo.*,
    ROW_NUMBER() OVER (
        ORDER BY 
            Foo.MeetingDate DESC
    ) AS RowOrder

As opposed to (when @SortType is 2):

  SELECT Foo.*,
    ROW_NUMBER() OVER (
        ORDER BY 
            Foo.FK_Status ASC,
            Foo.MeetingDate DESC
    ) AS RowOrder

Upvotes: 1

Views: 161

Answers (2)

user359040
user359040

Reputation:

The RowOrder column will be in the sequence described, but without an order by clause at the end of the query, there is no guarantee in what order the rows will be sorted.

So:

SELECT Foo.*,
       ROW_NUMBER() OVER (ORDER BY 
                          CASE @SortType WHEN 2 THEN Foo.FK_Status END ASC,
                          Foo.MeetingDate DESC) AS RowOrder
FROM Foo;

- could appear in any order, while:

SELECT Foo.*
FROM Foo
ORDER BY CASE @SortType WHEN 2 THEN Foo.FK_Status END ASC, Foo.MeetingDate DESC;

- will order the output in the sort order described (without including a RowOrder column).

Upvotes: 1

mustaccio
mustaccio

Reputation: 18945

CASE @SortType WHEN 2 THEN Foo.FK_Status END is equivalent to CASE @SortType WHEN 2 THEN Foo.FK_Status ELSE NULL END. In other words, if @SortType = 1 then the query effectively is this:

SELECT Foo.*,
    ROW_NUMBER() OVER (
        ORDER BY 
            NULL ASC,
            Foo.MeetingDate DESC
    ) AS RowOrder

Upvotes: 2

Related Questions