CODEWITHSUNDEEP
sqlsql-server
Rohit
Rohit

Reputation: 10296

Selecting single row out of multiple ones

Here is my SQL query 

SELECT 
    a.recordID b.recordState
FROM
    RecordTable a
INNER JOIN 
    RecordStateTable b ON a.recordID = b.recordID

The output of this query is

RecordID      RecordState
--------------------------
record1       passed
record2       passed
record3       passed
record3       failed
record4       passed

In the result I am getting two values for record3 i.e passed and failed. I want to discard such rows that has values both passed and failed and select only the failed ones.

I need to get only record1, record2, record4 in my final result. I don't want record3 because it contains both values passed and failed.

Upvotes: 1

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Your JOIN is unnecessary. The simplest way to write your query is:

SELECT rst.recordID, MAX(rst.recordState) as recordState
FROM RecordStateTable rst
GROUP BY rst.recordID;

Notes:

  • Use table aliases that are abbreviations for the tables. This makes it easier to read the queries.
  • You need a GROUP BY.
  • The JOIN is unnecessary because recordID is in the same table as recordState.

Upvotes: 0

jarlh
jarlh

Reputation: 44805

Several ways, one is using GROUP BY:

select RecordID, max(RecordState)
from RecordTable a
inner join RecordStateTable b
    on a.recordID = b.recordID
group by RecordID

Will work because passed > failed.

Upvotes: 4

Related Questions