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 — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Sep 9, 2013 at 6: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
– Damien_The_Unbeliever, Commented Sep 9, 2013 at 6:44

Aziz Shaikh · Accepted Answer · 2013-09-09 07:17:38Z

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 9, 2013 at 7:17

answered Sep 9, 2013 at 6:47

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

garvit gupta Over a year ago

I am using sql server 2008, is this approach works fine even in sql server 2005?

Aziz Shaikh Over a year ago

@garvitgupta yes it should work without any issues in sql server 2005 too.

garvit gupta Over a year ago

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 Over a year ago

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.

Aziz Shaikh Over a year ago

@garvitgupta I have updated that link of working demo, please try again. Update link is: sqlfiddle.com/#!3/ce957/1

|

ypercubeᵀᴹ · Accepted Answer · 2013-09-09 07:11:09Z

1

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

edited Sep 9, 2013 at 7:11

answered Sep 9, 2013 at 6:51

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Comments

TechDo · Accepted Answer · 2013-09-09 06:43:33Z

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 9, 2013 at 6:43

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

2 Comments

garvit gupta Over a year ago

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

TechDo Over a year ago

What should be the result if any of the column value is NULL?

Yosi Dahari · Accepted Answer · 2013-09-09 06:46:37Z

0

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

answered Sep 9, 2013 at 6:46

Yosi Dahari

7,0395 gold badges26 silver badges48 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:05:11Z

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, 2017 at 12:05

CommunityBot

11 silver badge

answered Sep 9, 2013 at 6:51

Jacky Cheng

1,5561 gold badge10 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to get the minimum value from the columns

5 Answers 5

7 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related