This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 80%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK%3C/text%3E%3C/svg%3E)
Hi Everyone,
Can I request you to clarify on the below confusion please with SQL server row_number with partition by clause?
Can we use the different table columns in the partition by clause while generating the row numbers ?
Ex : Select row_number() over (partition by table1.column1 , table2.column1 order by
Table2.column1)
From
Table1
Inner join table2 on table1.id=table2.id
This query is giving the wrong row numbers . Is there any limitation to not to use the multiple table columns in the partition by. In the above query table2.column1 is a date column which can have duplicates ..
Thank you.
It seems to work. Which row numbers do you expect?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
What do you mean by "wrong row numbers"?
Since your join is not your partition clause, and you said table2.column1 has duplicates, you are likely getting a random row selected from table2 when there are duplicate rows with the same date.
Hi ,
Thanks for the response.
In the above example, we have the data duplicates in both the partition by columns. So what's happening is , every time i execute the query the row numbers are shifting the values.
Select row_number() over (partition by table1.column1 , table2.column1 order by Table2.column1 DESC)
From
Table1
Inner join table2 on table1.id=table2.id
As I said, since you are partitioning on the ORDER BY column, the ORDER BY has no effect, and the numbers you get are non-deterministic.
Overall, you will only get a deterministic numbering if your PARTITION BY and ORDER BY columns constitute a unique key.
Yes, there are no limitations on where the partitioning columns come from.
Since we don't see your full query, we can't say exactly what is wrong. However, in your post, I notice this:
over (partition by table1.column1 , table2.column1 order by
Table2.column1)
That is, you have the same column in the PARTITION BY clause as in the ORDER BY clause. This only makes sense if you want to state that you don't care about the order. (And in that case I prefer to say ORDER BY (SELECT 1) instead. The PARTITION BY clause states that every combo the row numbering should start at 1 and be ordered by the columns in the ORDER BY clause. But if you have a column from the PARTITION BY clause in ORDER BY, that column has the same value throughout the partitioning and does not contribute to ordering.
Hi @kirankumar-3620,
> Is there any limitation to not to use the multiple table columns in the partition by.
No, it is supported. Could you share us your table structure and some sample data along with your expected result?
Best regards,
Cathy