SQL Row_Number reverse order

Question

I am using SQL Row_Number to get a raking column;

ROW_NUMBER() OVER(PARTITION BY PolicyRef@ ORDER BY di.Create_date,di.Create_time_hh,di.Create_time_mm) as [Rank]

However I need to reverse the order. I've tried adding in Asc but that doesn't seem to make any difference?!

PolicyRef@ is varchar(255), Create_Date is a datetime, but with the time part all as 00.00.00.000, Create_Time_hh is a number (this is the hour in the day the record was created, but stored as 09, 17 etc) and Create_Time_mm is also a number (this is the minut in the day the record was created, but again stored as 19, 57 etc).

Why can I not reorder this in the usual way?

Thanks

If you want to reverse the order you need to use DESC. ASC is the default sort order (also: which DBMS are you using?) — user330315
– user330315, Commented Nov 11, 2015 at 12:48
That doesn't seem to change anything either! How do I find out the DBMS? — Becky
– Becky, Commented Nov 11, 2015 at 12:50
@Becky - DBMS - which specific product are you working with? sql-server? oracle? mysql? Something else? Any question tagged sql should, in general, also have a tag for what product you're working with. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 11, 2015 at 13:18

Community · Accepted Answer · 2017-05-23 12:31:19Z

1

Use "desc" to order descending. Ascending (asc) is the default and will be ordered by ASC if not declared.

Maybe this might be useful? OVER clause. How to order by multiple columns within a CASE clause?

You can also order fields by multiple columns, i.e

SELECT Column1, Column2, Column3
FROM Table1
Order By Column1 desc, Column2 desc

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Nov 11, 2015 at 12:52

Josh Harris

4464 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Becky Over a year ago

Ordering by desc doesn't change it, which I don't understand!

Juan Carlos Oropeza Over a year ago

well you have three field whichi one you have desc? one or all three? ORDER BY di.Create_date DESC, di.Create_time_hh DESC, di.Create_time_mm DESC

Becky Over a year ago

OH! I didn't realise you needed it for every column, brilliant that's done the trick, thank you so much!

Collectives™ on Stack Overflow

SQL Row_Number reverse order

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related