CODEWITHSUNDEEP
sqlsql-server
MrWhale
MrWhale

Reputation: 15

How to return top and bottom row in one query?

I am trying to write a query that will produce the top row and bottom row in one query. I can find one or the other but I cant get both in one row.

Here is what I have: 

SELECT (SELECT top(1) Lastname + ',' + firstname FROM
CUSTOMERS 
     join orders on customerID = customerID_fk 
     join orderDetails on orderID = orderID_fk
group by Lastname + ',' + firstname
order by sum(quantity) desc);

Here is a link: http://sqlfiddle.com/#!6/51ad4/129

What is the best practice to get the return I am looking for?

Upvotes: 1

Views: 130

Answers (3)

Ashish Charan
Ashish Charan

Reputation: 2387

Possibly we can use sub query for this. The idea is to find out the Max and Minimum ordered items. Then we can use existing query and write something like sum(quantity) in (Sub Query Here).

Or can you check if the following works?

SELECT (SELECT Top(2) Lastname + ',' + firstname FROM
CUSTOMERS 
     join orders on customerID = customerID_fk 
     join orderDetails on orderID = orderID_fk
group by Lastname + ',' + firstname
order by sum(quantity) desc having sum(quantity) = 
Max(Sum(quantity)) or Sum(quantity)=Min(Sum(Quantity))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Here is one way using window functions:

select name
from (SELECT Lastname + ',' + firstname as name,
             row_number() over (order by sum(quantity)) as rownum,
             count(*) over () as cnt
      FROM CUSTOMERS 
           join orders on customerID = customerID_fk 
           join orderDetails on orderID = orderID_fk
      group by Lastname + ',' + firstname
     ) t
where rownum = 1 or rownum = cnt;

Here is another way:

with cte as (
      SELECT Lastname + ',' + firstname as name, sum(quantity) as qty
      FROM CUSTOMERS 
           join orders on customerID = customerID_fk 
           join orderDetails on orderID = orderID_fk
      group by Lastname + ',' + firstname
)
select top 1 name from cte order by qty union all
select top 1 name from cte order by qty desc;

Upvotes: 2

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

I think you are looking for Union

SELECT top(1) Lastname + ',' + firstname FROM
CUSTOMERS 
     join orders on customerID = customerID_fk 
     join orderDetails on orderID = orderID_fk
group by Lastname + ',' + firstname
order by sum(quantity) desc Union
SELECT top(1) Lastname + ',' + firstname FROM
CUSTOMERS 
     join orders on customerID = customerID_fk 
     join orderDetails on orderID = orderID_fk
group by Lastname + ',' + firstname
order by sum(quantity) asc

Upvotes: -1

Related Questions