Menu
I am fighting with the distinct keyword in sql. I just want to display all row numbers of unique (distinct) values in a column & so I tried:
SELECT DISTINCT id, ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM table WHERE fid = 64 however the below code giving me the distinct values:
SELECT distinct id FROM table WHERE fid = 64 but when tried it with Row_Number.
then it is not working.
298
ROW_NUMBER adds a unique incrementing number to the results grid. The order, in which the row numbers are applied, is determined by the ORDER BY expression.
In my experience, an aggregate (DISTINCT or GROUP BY) can be quicker then a ROW_NUMBER() approach. Saying that, ROW_NUMBER is better with SQL Server 2008 than SQL Server 2005. However, you'll have to try for your situation.
The SQL SELECT DISTINCT StatementThe SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
Bare usage of DISTINCT will return the first occurrence. However, it can work either way by sorting the initial results first before conducting the distinction step.
This can be done very simple, you were pretty close already
SELECT distinct id, DENSE_RANK() OVER (ORDER BY id) AS RowNum FROM table WHERE fid = 64 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
