CODEWITHSUNDEEP
sqlsql-servert-sql
fearofawhackplanet
fearofawhackplanet

Reputation: 53396

SQL server return only single row or null

I have a SQL query which I only ever want to return 1 row. If there are multiple rows, I want to return null.

I guess I can do it with something like

SELECT whatever FROM MyTable
 WHERE something = myQuery
   AND ( COUNT(SELECT whatever FROM MyTable
             WHERE something = myQuery) = 1)

but that's pretty nasty and I wondered if there as a neater way of doing it.

Upvotes: 4

Views: 2958

Answers (5)

SHD
SHD

Reputation: 409

We can add it this way too,

SELECT max(whatever) as whatever
FROM MyTable
WHERE something = myQuery
HAVING  COUNT(*) = 1

Upvotes: 0

Neil
Neil

Reputation: 1

set rowcount 1

Before the select statement.

Later,

set rowcount 0

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181350

Well, another way may be:

select f1, f2, f3
  from your_table
 where f4 = f5*2
 group by f1, f2, f3
having count(1) = 1

Don't know if it's neater than your solution though.

Upvotes: 1

Oded
Oded

Reputation: 499132

You can use an IF clause to check for a single row and only select if that's the case:

IF SELECT COUNT(*) FROM MyTable WHERE something = myQuery) = 1
  BEGIN
    SELECT whatever FROM MyTable
     WHERE something = myQuery
  END

Upvotes: 1

RPM1984
RPM1984

Reputation: 73113

Cant you do: 

SELECT whatever FROM
    (
       SELECT whatever, COUNT(*) As NumRecords
       FROM MyTable
       WHERE something = myQuery
       GROUP BY whatever
       HAVING NumRecords = 1
    )

Upvotes: 6

Related Questions