CODEWITHSUNDEEP
sqlsql-serversubquery
Ehsan Akbar
Ehsan Akbar

Reputation: 7299

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. in subquery sqlserver

I want to execute this query in my database.As you can see both tables A and B has one-many relations ,but i need the latest record in B.so i here is my query :

select *,(select top 1 ResultTest ,ResultState2 from B where GasReceptionId=A.Id order by Id desc) 
from A where OrganizationGasId= 4212

But i get this error

Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 0

Views: 847

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

A subquery in the SELECT clause must return exactly one column (and one or zero rows). So you can either have two subqueries:

select 
  a.*,
  (select top 1 resulttest from b where gasreceptionid = a.id order by id desc) as test,
  (select top 1 resultstate2 from b where gasreceptionid = a.id order by id desc) as state
from a 
where a.organizationgasid = 4212;

Or, much better, move the subquery to the FROM clause. One way is OUTER APPLY:

select 
  a.*, r.resulttest, r.resultstate2
from a 
outer apply
(
  select top 1 resulttest, resultstate2
  from b 
  where gasreceptionid = a.id 
  order by id desc
) r
where a.organizationgasid = 4212;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

You can rephrase this query as a basic join which uses an analytic function (e.g. row number) to identify the correct row's data from B to include with each record coming from the A table.

SELECT *
FROM
(
    SELECT a.*, b.ResultTest, b.ResultState2,
        ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY a.ID DESC) rn
    FROM A a
    LEFT JOIN B b
        ON a.Id = b.GasReceptionId
    WHERE
        a.OrganizationGasId = 4212
) t
WHERE t.rn = 1;

Upvotes: 1

Related Questions