Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query

Posted on 2012-04-01
4
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 20

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 1500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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