4

I have a lot of fairly large datasets in a MS SQL table, and I want to get a bunch of data about.

For example, let's say I have

Name Age Height Weight Gender Country
Person 23 186 79 Male GB
Person1 25 172 65 Male US
Person2 27 186 79 Female GB
Person3 19 187 68 Female GB

And I want to go off and query to get the oldest male, the tallest male and heaviest male which were uploaded today. E.g. something like

select top 1 name, height from 'people' where country = 'GB' and gender = 'male' order by height desc;

select top 1 name, age from 'people' where country = 'GB' order by age desc

Note, I don't need to return the row, I just need to return the person and each SELECT will only return one row.

I'm currently running many, individual, SELECT statements but these are very expensive as I have to connect to the DB each time and the process the result.

So, ideally, I would like to merge the results of them all to return:

Oldest Tallest Heaviest
Person Person Person

Is this possible?

1 Answer 1

2

You can combine the queries like this

select (select top 1 name from people where country = 'GB' and gender = 'male' order by height desc, Name) Tallest, 
       (select top 1 name from people where country = 'GB' order by age desc, Name) Oldest

But that query plan won't be optimized across the different queries. Ie you won't see a single scan accumulating all the different queries; however each subquery may use a separate optimized access path and take advantage of indexes.

2
  • Ah, I might be falling foul of my simplified example. Each select query also has a where and an order by
    – Dan
    Mar 2 at 17:42
  • Done, I think..
    – Dan
    Mar 2 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.