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
Solved

SQL Query

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 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