Solved

SQL Query

Posted on 2012-04-01
4
334 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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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