Sort by row number unique values erroneous error

[Anonymous]

I have a sql view that contains a column for ID, date, value, and row number. I want to show the data sorted by ID and date, but when the value column is added it changes the sort to ID and value. I tried to use sort by another column to have the ID sorted by the row number but I get an error that says the row number value needs to be unique for each id value. Row number column in the view is [ROW_NUMBER() OVER(PARTITION BY id ORDER by date) as row#] so I know its unique. What gives?

[HotChilli · Accepted solution]

I think you're misunderstanding the message.  "error that says the row number value needs to be unique for each id value" - that's not what it says. 

It says "There can't be more than one value in row# for the same value in id" so it's looking at , for example id = a and saying, "right, what value do i use to sort this" and it gets 1,2,3,4,5,6 etc. so it doesn't know what to do. What it wants is 1 value for "a", 1 value for "b" and so on.

Now, all that explanation doesn't really help you sort your data but at least you'll know what's happening.