By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,477 Members | 1,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,477 IT Pros & Developers. It's quick & easy.

Subselect top1 - need two columns.

P: 5
I have a subselect in my select statement:
select t1.a, t1.b, (select top 1 t2.a from where)

what I need is:
select t1.a, t1.b, (select top 1 t2.a, t2.b from where)

However, I get the error that I can only have one field.

Here is the sql. I'm stumped. Thanks for your help...

Select
cm.ChildStateID,
pc.DateOfService,

(SELECT
TOP 1 pcr.specialtyid , sp.Description
FROM
ProviderAccount pa,
ProviderPayee pp,
Provider pr,
ProviderCredential pcr,
Specialty sp
WHERE
pca.ProviderID = PP.TaxID
and right(pca.ProviderSuffix,4) = substring(pa.PayeeSeq,1,4)
and PP.ID = PA.PayeeID
and PA.ProviderID = PR.ID
and pcr.ProviderID = PR.id
and pcr.SpecialtyID = sp.id)

from
Childmaster cm,
pclaim pc,
pclaimaux pca

where
pca.childid = cm.childstateid
and pc.id = pca.pclaimid

order by
cm.ChildStateID,
pc.DateOfService
Feb 14 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
What version of SQL Server are you using?

As you can see in this samples, TOP keyword can work in multiple columns.

-- CK
Feb 14 '08 #2

P: 5
Yes, I see it works for multiple columns but not in a sub-select.

SQL Server 2000.

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Feb 14 '08 #3

ck9663
Expert 2.5K+
P: 2,878
I have a subselect in my select statement:
select t1.a, t1.b, (select top 1 t2.a from where)

what I need is:
select t1.a, t1.b, (select top 1 t2.a, t2.b from where)

However, I get the error that I can only have one field.

Here is the sql. I'm stumped. Thanks for your help...

Select
cm.ChildStateID,
pc.DateOfService,

(SELECT
TOP 1 pcr.specialtyid , sp.Description
FROM
ProviderAccount pa,
ProviderPayee pp,
Provider pr,
ProviderCredential pcr,
Specialty sp
WHERE
pca.ProviderID = PP.TaxID
and right(pca.ProviderSuffix,4) = substring(pa.PayeeSeq,1,4)
and PP.ID = PA.PayeeID
and PA.ProviderID = PR.ID
and pcr.ProviderID = PR.id
and pcr.SpecialtyID = sp.id)

from
Childmaster cm,
pclaim pc,
pclaimaux pca

where
pca.childid = cm.childstateid
and pc.id = pca.pclaimid

order by
cm.ChildStateID,
pc.DateOfService

Sorry I missed that part.It looks to me you're trying to include the result of your subquery to every record on your main query. Can you see if this will work?

Expand|Select|Wrap|Line Numbers
  1. Select 
  2.    cm.ChildStateID, 
  3.     pc.DateOfService,
  4. from
  5. (Select  
  6.     '1' as mainlink, cm.ChildStateID, 
  7.     pc.DateOfService,        
  8. from   
  9.     Childmaster cm,
  10.     pclaim pc,   
  11.     pclaimaux pca    
  12. where  
  13.     pca.childid = cm.childstateid 
  14.     and pc.id = pca.pclaimid ) MainQuery, 
  15.  
  16. (SELECT TOP 1 '1' as sublink, pcr.specialtyid , sp.Description
  17.          FROM          
  18.    ProviderAccount pa,
  19.    ProviderPayee pp,
  20.    Provider pr,
  21.    ProviderCredential pcr,
  22.    Specialty sp
  23.          WHERE      
  24.    pca.ProviderID = PP.TaxID 
  25.    and right(pca.ProviderSuffix,4) = substring(pa.PayeeSeq,1,4)
  26.    and PP.ID = PA.PayeeID 
  27.    and PA.ProviderID = PR.ID     
  28.    and pcr.ProviderID = PR.id
  29.    and pcr.SpecialtyID = sp.id) SubQuery
  30.  
  31. where Mainquery.mainlink = Subquery.sublink
  32.  
  33. order by 
  34.     ChildStateID, 
  35.     DateOfService
  36.  
The objective:
1. Run both query as both subquerries.
2. Create an artificial key that will join the 2.
3. Join them using where (you can also use INNER JOIN

Good luck.

-- CK
Feb 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.