T-SQL SELECT TOP 1
I am running a query where I want to select the first row from the query result. If I have two rows in the table it will return a result, but if I only have one it will return nothing.
select TOP 1 u.ID
from [dbo].[User] u
where u.ID <> '5dc89076-e554-42f2-a9ae-787b20f6f56b' AND u.gender != 'male'
except
select [dbo].[Like].likes
from [dbo].[Like]
where [dbo].[Like].[user] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'
except
select [dbo].[Dislike].dislikes
from [dbo].[Dislike]
where [dbo].[Dislike].[user] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'
This query returns nothing
select u.ID
from [dbo].[User] u
where u.ID <> '5dc89076-e554-42f2-a9ae-787b20f6f56b' AND u.gender != 'male'
except
select [dbo].[Like].likes
from [dbo].[Like]
where [dbo].[Like].[user] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'
except
select [dbo].[Dislike].dislikes
from [dbo].[Dislike]
where [dbo].[Dislike].[user] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'
This query returns Id: 9EF5B83E-319A-4E2F-88A1-E67227DBFDCE
source to share
select TOP 1 u.ID
from ...
except
...
means it first runs the first selection, then takes the first result, and finally runs the second and third selections to determine what to exclude.
This is not what you want. You want to run three queries, exclude rows, and finally take the first result.
One correct syntax for this is
select top 1 ID
from (
select u.ID
from ...
except
...
) as q
There are other approaches that can give the same result, but this is the one I would go with.
source to share
Or you can use a simple OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY keyword
Note: This requires an ORDER BY clause.
CREATE TABLE #test (
Id INT,
Name varchar(50)
)
GO
INSERT INTO #test VALUES
(1, 'Soda'),
(2, 'Coke'),
(3, 'Beer'),
(4, 'Wine')
GO
SELECT *
FROM #test
ORDER BY Id
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
GO
DROP TABLE #test;
GO
source to share