CODEWITHSUNDEEP
sql
user1040110
user1040110

Reputation: 21

How to return smallest value inside the resultset as a separate column in SQL?

I've been struggling with the following SQL query.

My resultset is now:

| Id | Customer | Sales |
| 1  | 1        | 10    |
| 2  | 1        | 20    |
| 3  | 2        | 30    |
| 4  | 2        | 40    |

What I'd like to do is to add additional column that shows the smallest sale for that customer:

| Id | Customer | Sales | SmallestSale |
| 1  | 1        | 10    | 10           |
| 2  | 1        | 20    | 10           |
| 3  | 2        | 30    | 30           |
| 4  | 2        | 40    | 30           |

As the select query to get those three columns is now rather complex I'd like to avoid subqueries.

Any ideas?

Mika

Upvotes: 2

Views: 55

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

select s.Id, s.Customer, s.Sales, sm.SmallestSale 
from Sales s
inner join (
    select Customer, min(sales) as SmallestSale 
    from Sales
    group by Customer
) sm on s.Customer = sm.Customer

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453308

Assuming your RDBMS supports windowed aggregates

SELECT  Id, 
        Customer,
        Sales, 
        MIN(Sales) OVER (PARTITION BY Customer) AS SmallestSale 
FROM YourTable

Upvotes: 4

Related Questions