Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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!
0
himabindu_nvn
Asked:
himabindu_nvn
  • 2
1 Solution
 
Daniel Van Der WerkenIndependent ConsultantCommented:
I don't know of any elegant solution to do what you want right off.

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.
0
 
jogosCommented:
Question does not explain on how the column 'output' must get it values. It takes a good observation to see where the content of 'output' comes f.

The comment of Dan7el is close  with the rownumber. But rownumber is unique, in example it's not.
So DENSE_RANK is the alternative that can get same 'rownumber' for same values
See syntax and example at
http://msdn.microsoft.com/en-us/library/ms173825.aspx
0
 
himabindu_nvnAuthor Commented:
Thanks!
0
 
himabindu_nvnAuthor Commented:
Here's the query

select n1,n2,n3,n4,Dense_RANK() over (ORDER BY n1,n2,n3,n4 DESC) AS 'Rank'
from naveen1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now