Run multiple, expensive, single result queries and return a single row



  • 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?



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2