views:

179

answers:

3

I have a table that has records with a structure similar to this..

ID RoleID
1 NULL
2 15
3 16

I wrote a where clause to get records like the following

SELECT * from TableX 
WHERE (RoleID = 2 OR RoleID IS NULL)

This gets me the record of "1,NULL"

But if i query

SELECT * from TableX 
WHERE (RoleID = 15 OR RoleID IS NULL)

I get back "1,NULL" and "2,15".

Does anyone know how to structure a select to give me only one record? I only want "2,15" if 15 was passed and "1,NULL" if there are no matches.

Note, the actual query has MANY more where clauses to it, so nesting itself inside itself would be a very big query.

A: 
SELECT TOP 1 * from TableX WHERE (RoleID = 15 OR RoleID IS NULL)
ORDER BY RoleID DESC
Jeff O
A: 
order by RoleID limit 1
Thom Smith
this is not valid SQL Server syntax
KM
+2  A: 

How about SELECT TOP 1 with ORDER BY RoleID DESC

Here is a working example.

declare @mytable table
(
    ID int null,
    RoleID int null
)
insert @mytable values
(1, null),
(2, 15),
(3, 1)

select TOP 1 * 
from @mytable 
WHERE (RoleID = 2 OR RoleID IS NULL)
order by RoleID desc


select top 1 * from @mytable 
WHERE (RoleID = 15 OR RoleID IS NULL)
order by RoleID desc

Edit (edited based on comments received)
Note that the Insert statement works only for SQL Server 2008. For versions prior to 2008, you will have to break it into invidual inserts.

Raj More
+1, I like your example code. Also,the INSERT syntax is SQL Server 2008 syntax _only_ if you try running it on an older version you need 3 INSERTs
KM
Thanks! I have been using TOP 1 all morning to see the fields of my tables, don't know why I didn't think of it for the query itself.
Rob