himabindu_nvn
asked on
SQL Query
I have a table with 4 columns. Here is how the table looks.
Col1 Col2 Col3 Col4 output
100 101A 200 9A
100 101A 200 9A
100 101A 200 8E
100 101A 111 77
100 900B 993 77
100 900B 839 8E
100 900B 839 8E
200 101V 342 8J
200 909Y 929 9R
200 909Y 929 8U
Initially the output column doesn't have values in it or has null values. I want a query which gives the result as below.
Col1 Col2 Col3 Col4 Output
100 101A 200 9A 1
100 101A 200 9A 1
100 101A 200 8E 2
100 101A 111 77 3
100 900B 993 77 4
100 900B 839 8E 5
100 900B 839 8E 5
200 101V 342 8J 1
200 909Y 929 9R 2
200 909Y 929 8U 3
Thanks!
Col1 Col2 Col3 Col4 output
100 101A 200 9A
100 101A 200 9A
100 101A 200 8E
100 101A 111 77
100 900B 993 77
100 900B 839 8E
100 900B 839 8E
200 101V 342 8J
200 909Y 929 9R
200 909Y 929 8U
Initially the output column doesn't have values in it or has null values. I want a query which gives the result as below.
Col1 Col2 Col3 Col4 Output
100 101A 200 9A 1
100 101A 200 9A 1
100 101A 200 8E 2
100 101A 111 77 3
100 900B 993 77 4
100 900B 839 8E 5
100 900B 839 8E 5
200 101V 342 8J 1
200 909Y 929 9R 2
200 909Y 929 8U 3
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
ASKER
Here's the query
select n1,n2,n3,n4,Dense_RANK() over (ORDER BY n1,n2,n3,n4 DESC) AS 'Rank'
from naveen1
select n1,n2,n3,n4,Dense_RANK() over (ORDER BY n1,n2,n3,n4 DESC) AS 'Rank'
from naveen1
One way might be to "select based off row order" which isn't exactly a correct wording since selects don't really have a default order, but here is an article on this:
http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table
do the select and then based on criteria, you could write a case statement that would fill in the output column. It could even be as basic as CASE WHEN @RowNumber = 1 OR @RowNumber = 2 THEN 1 ELSE WHEN @RowNumbder = 3 THEN 2 END etc.