Retrieve single row by comparing with other rows for the same record

Question

0.00/5 (No votes)

See more:

SQL

I've a table with data. The rows are repeated but the change is in only one column(status). Let the status column data be (A, B, C).
Sometimes data will be available with status A, B and C . Then 3 rows will be created for one record(ID=2001). What I need is, If A,B,C are available, the query should return only the row where Status=' A'. Sometimes for another record (ID=2005) B &C will be status. Then I should get row Where Status='B'

How can i tackle this situation with SQLQuery? I'm using SQLServer2008. Any responses will be appreciated.

Posted 27-Jan-14 18:46pm

Ruggers

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Damian S_ · Answer 1 · 2014-01-27T19:21:00

Solution 1

If it's always the earliest (alphabetically) record that you want, you could do it like this:

SQL

select ID, min(Status) as Status 
from TABLENAME
group by ID
order by ID

Posted 27-Jan-14 19:21pm

_Damian S_

Comments

Ruggers 28-Jan-14 1:27am

Thank you for your response. Not like that. Its not alphabetical. The status which I mentioned is an example. Its words actually. So can we provide any priority for the words?

_Damian S_ 28-Jan-14 1:59am

Words can be alphabeticised... if it needs to be in a particular order, you could somehow work out that order and then do a min from that calculated field... eg:

select ID, min(case Status when 'A' then 1 else 2 end)...

Thava Rajan · Answer 2 · 2014-01-27T22:38:00

Solution 2

SQL

SELECT * 
FROM (
       SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [STATUS]) AS RNO, * 
       FROM  <yourtable> /*where ID =2001 AND[STATUS] IN ('A','B','C')*/
     ) X
WHERE RNO =1
</yourtable>

Hope this will also produce what you wnat

Posted 27-Jan-14 22:38pm

Thava Rajan

Jorge J. Martins · Answer 3 · 2014-01-28T00:12:00

Hi Thava
This is just a suggestion.
If you want priorities for the Status, you'll need an auxiliary table where you can set those priorities as a numeric type.
Then you can join this table with your main table adding a priority column to the result set.
Then you can group select again selecting the MAX(priority) (or MIN depending on how you set the priority order).
All this is done with a query and subqueries.