How to get the minimum value from the columns

Question

I have a table Item and I want to get the minimum price of the Item for the particular id

Table Item:

Id    Price1                Price2           Price3
1       10                    20                30
2       20                    30                40

According to the above example, the minimum price for id-1 is 10 and for id-2 is 20. I simply just want to get the minimum value from the three column for particular id.

Remember: I can't create the cases as, any column can be null. Thanks in advance.

Why can't you use case? Just put corresponding conditions like Price1 is not null; Price2 is not null etc — Dmitry Bychenko, Sep 09 '13 at 06:44
It's generally a sign of a poor database design if *multiple* columns are storing the same "kind" of data, such that it makes sense to compare their values. — Damien_The_Unbeliever, Sep 09 '13 at 06:44

Aziz Shaikh · Accepted Answer · 2013-09-09T07:17:38.867

2

One approach could be like this:

SELECT Id, MIN(Price) FROM (
   SELECT Id, Price1 As Price FROM Table1
   UNION ALL
   SELECT Id, Price2 As Price FROM Table1
   UNION ALL
   SELECT Id, Price3 As Price FROM Table1
) As AllValues
GROUP BY Id

This works even if there are null values. Here is the working demo.

edited Sep 09 '13 at 07:17

answered Sep 09 '13 at 06:47

Aziz Shaikh

16,245
11
62
79

I am using sql server 2008, is this approach works fine even in sql server 2005? – garvit gupta Sep 09 '13 at 07:14
@garvitgupta yes it should work without any issues in sql server 2005 too. – Aziz Shaikh Sep 09 '13 at 07:18
When I run this query, it throws error of Invalid column name id, really strange, don't know why. I am typing the coulmn name exactly correct. – garvit gupta Sep 09 '13 at 07:18
Here you give the link of working demo, I copied the "Create" statement query and try to build a schema on my local, and again it throw an error of "Incorrect syntax near 'auto_increment", it looks really strange. – garvit gupta Sep 09 '13 at 07:20
@garvitgupta I have updated that link of working demo, please try again. Update link is: http://sqlfiddle.com/#!3/ce957/1 – Aziz Shaikh Sep 09 '13 at 07:21
Yes I have done the same updation on my local, now it is working fine. – garvit gupta Sep 09 '13 at 07:26
Your approach gives me 2 rows, what should I have to do if I want to select only one row at a time on the basis of id? – garvit gupta Sep 09 '13 at 07:54

ypercubeᵀᴹ · Answer 2 · 2013-09-09T07:11:09.537

Two similar solutions, using APPLY operator:

SELECT t.Id,
       MIN(m.Price)
FROM   
    tableX AS t
  CROSS APPLY
    ( SELECT Price = Price1 UNION
      SELECT Price2 UNION
      SELECT Price3 
    ) AS m
GROUP BY t.Id ;



SELECT t.Id,
       x.Price
FROM   
    tableX AS t
  OUTER APPLY
    ( SELECT TOP (1) Price
      FROM 
       ( SELECT Price1 UNION
         SELECT Price2 UNION  
         SELECT Price3
       ) AS m (Price)
       WHERE Price IS NOT NULL
       ORDER BY Price ASC
    ) x ;

Tested at SQL-Fiddle

score 0 · Answer 3 · answered Sep 09 '13 at 06:43

0

Please try:

Select Id,
       Case When Price1 < Price2 And Price1 < Price3 Then Price1
            When Price2 < Price1 And Price2 < Price3 Then Price2 
            Else Price3
            End As TheMin
From   
    YourTable

OR

select 
    Id, 
    MIN(Price) TheMin
FROM
(
    select Id, Price1 Price From YourTable
    union all
    select Id, Price2 Price From YourTable
    union all
    select Id, Price3 Price From YourTable
)x group by Id

answered Sep 09 '13 at 06:43

TechDo

18,398
3
51
64

I already mentioned that I can't use cases, as any of the three columns can be null, so to handle null we again have to make many cases. Thats why I am ignoring this logic – garvit gupta Sep 09 '13 at 06:46
What should be the result if any of the column value is `NULL`? – TechDo Sep 09 '13 at 06:49

score 0 · Answer 4 · answered Sep 09 '13 at 06:46

Take maximum value for NULL:

Select Id,
       Case When ISNULL(Price1,MaxPrice) < ISNULL(Price2,MaxPrice)  And ISNULL(Price1,MaxPrice)  < ISNULL(Price3,MaxPrice)  Then ISNULL(Price1,MaxPrice)
            When ISNULL(Price2,MaxPrice)  < ISNULL(Price1,MaxPrice)  And ISNULL(Price2,MaxPrice)  < ISNULL(Price3,MaxPrice)  Then ISNULL(Price2,MaxPrice)  
            Else ISNULL(Price3,MaxPrice) 
            End As TheMin
From   
    YourTable

When MaxPrice could be calculated or just max int...

score 0 · Answer 5 · edited May 23 '17 at 12:05

0

take a look at this post. My though is that you could invert the table column & row and simply use min() to get the minimum.

Select MYSQL rows but rows into columns and column into rows

(All credit to Anax in the linked post)

edited May 23 '17 at 12:05

Community

1
1

answered Sep 09 '13 at 06:51

Jacky Cheng

1,536
1
10
22

How to get the minimum value from the columns

5 Answers5