Solved

SQL Query

Posted on 2012-04-01
4
328 Views
Last Modified: 2012-04-01
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
Comment
Question by:himabindu_nvn
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Daniel Van Der Werken
ID: 37793934
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
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37793953
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
 

Author Closing Comment

by:himabindu_nvn
ID: 37793978
Thanks!
0
 

Author Comment

by:himabindu_nvn
ID: 37793979
Here's the query

select n1,n2,n3,n4,Dense_RANK() over (ORDER BY n1,n2,n3,n4 DESC) AS 'Rank'
from naveen1
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now