SQL multiple select from another select query

Question

Is there any way to do selects from a single Select in Microsoft SQL Server?

Something like :

SELECT id, type, name, address  
FROM Persons AS Persons_DATA 
WHERE (name='...' OR name in ('...','...'))

SELECT TOP 1 'Best Vendor : '+name from Persons_DATA where type=1 order by ...
union SELECT TOP 1 'Best Customer : '+name from Persons_DATA where type=2 order by ...
union SELECT TOP 1 'Least Debt : '+name from Persons_DATA where ... order by ...
union SELECT TOP 1 'Most Debt : '+name from Persons_DATA where ... order by ...
...

I know I can do :

SELECT TOP 1 
    'Best Vendor : '+name 
FROM
    Persons 
WHERE
    type = 1 AND (name='...' OR name in ('...','...')) 
ORDER BY ...

UNION

SELECT TOP 1 
    'Best Customer : '+name 
FROM
    Persons 
WHERE
    type = 2 
    AND (name='...' OR name in ('...','...')) 
ORDER BY ...

But I think this will take more time because it will do many unnecessary searches in a big data base

but the first code will only do 1 search in the big database and X searches in a small database and it will take less time.... am I right?

TOP without an ORDER BY? I don't understand what are you trying to do there. — Ilyes
– Ilyes, Commented Jun 13, 2019 at 9:24
Actually its just some test code the actual question is I want to do some sub-selects from a select — Azrideus
– Azrideus, Commented Jun 13, 2019 at 9:25

Ponmani Chinnaswamy · Accepted Answer · 2019-06-13 09:38:23Z

2

You can use CASE WHEN statement..

SELECT CASE WHEN type=1 THEN 'Best Vendor : '+name 
   WHEN type=2 THEN 'Best Customer: '+name 
    WHEN type=3 THEN 'Least Debt: '+name 
     WHEN type=4 THEN 'Most Debt: '+name ELSE '....'  END  
from Persons  A 
LEFT JOIN (SELECT RANK() OVER (PARTITION BY type ORDER BY id DESC) Rk, name from Persons    
        WITH(NOLOCK) ) AS PS  ON PS.Id = A.id   and Rk=1

edited Jun 13, 2019 at 9:38

answered Jun 13, 2019 at 9:31

Ponmani Chinnaswamy

85910 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Azrideus Over a year ago

Oh thanks i think this will solve my problem but I guess you did a small mistake in CASE WHEN Syntax : w3schools.com/sql/sql_case.asp

Azrideus Over a year ago

Is there anyway to contain a count() or sum() in this querry? and I also dont understand this

LEFT JOIN (SELECT RANK() OVER (PARTITION BY type ORDER BY id DESC) Rk, name from Persons             WITH(NOLOCK) ) AS PS  ON PS.Id = A.id   and Rk=1

Ponmani Chinnaswamy Over a year ago

which count you want? type wise OR total sum?

Azrideus Over a year ago

select count of total rows with type 2 something like : Least debt : Customer , Total customers with debt : 100

Ponmani Chinnaswamy Over a year ago

Left join takes the latest record, instead of top 1 in each types

|

Collectives™ on Stack Overflow

SQL multiple select from another select query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related