CODEWITHSUNDEEP
sql-server
BumbleBee
BumbleBee

Reputation: 10779

Sorting the sorted data in sql server

The query inside *** and *** will produce the partitioned rows ordered by status_date. Now, if that result has more than one row with same status_date then I want to sort only those row by change_date.

I have come up with the below query but it's not giving the desired result.

 SELECT  MID,status_date,status,change_date, row_number() OVER (PARTITION BY MID ORDER BY  change_date DESC )  as RN1, 1 as TimeRange
                    FROM 
                        (
                    *** SELECT MID,status_date,status,change_date,
                        rn = row_number() OVER (PARTITION BY MID ORDER BY status_date DESC)
                        FROM listing_History   (nolock)
                         WHERE  MID like  '12-572561'  ****

                        )  AS A

For Reference :

The results from query inside *** and *** : 

12-572561   2012-06-11 00:00:00.000 Canceled    2012-06-11 14:53:22.957  1
12-572561   2012-03-01 00:00:00.000 Canceled    2012-03-01 13:32:40.030  2
12-572561   2012-01-02 00:00:00.000 Active      2012-01-02 07:32:34.067  3
12-572561   2012-01-02 00:00:00.000 Active      2012-06-11 07:43:29.400  4
12-572561   2012-01-02 00:00:00.000 Active      2012-01-10 00:01:03.407  5
12-572561   2012-01-02 00:00:00.000 Active      2012-06-11 07:43:29.417  6

The results with the existing query :

12-572561   2012-06-11 00:00:00.000 Canceled    2012-06-11 14:53:22.957  1  1
12-572561   2012-01-02 00:00:00.000 Active      2012-06-11 07:43:29.417  2  1
12-572561   2012-01-02 00:00:00.000 Active      2012-06-11 07:43:29.400  3  1
12-572561   2012-03-01 00:00:00.000 Canceled    2012-03-01 13:32:40.030  4  1
12-572561   2012-01-02 00:00:00.000 Active      2012-01-10 00:01:03.407  5  1
12-572561   2012-01-02 00:00:00.000 Active      2012-01-02 07:32:34.067  6  1

Desired results :

12-572561   2012-06-11 00:00:00.000 Canceled    2012-06-11 14:53:22.957  1  1
12-572561   2012-03-01 00:00:00.000 Canceled    2012-03-01 13:32:40.030  2  1
12-572561   2012-01-02 00:00:00.000 Active      2012-06-11 07:43:29.417  6  1
12-572561   2012-01-02 00:00:00.000 Active      2012-06-11 07:43:29.400  4  1
12-572561   2012-01-02 00:00:00.000 Active      2012-01-10 00:01:03.407  5  1
12-572561   2012-01-02 00:00:00.000 Active      2012-01-02 07:32:34.067  3  1

Upvotes: 1

Views: 97

Answers (2)

zitta
zitta

Reputation: 77

You can use ORDER BY for change_date at the end of the query

Upvotes: 2

bobs
bobs

Reputation: 22184

The row_number() function doesn't determine order for the results. You need an ORDER BY clause. If you don't need the row_number() values in the results, you can do the following

SELECT MID,status_date,status,change_date
FROM listing_History   (nolock)
WHERE  MID like  '12-572561'
ORDER BY status_date DESC, change_date DESC

Upvotes: 2

Related Questions