Select min value not including 0

Transact-SQL
1

How can I select min(price) but I don't want 0 to count (so if there is a 0 I want it to select the next min price)

2

having min(price)>0

3

the reason that won't work is because i am doing select min(price),max(price)

and i want the max(price) even if min(price is 0 - if it's 0 I want the next min price

4

MIN ( NULLIF ( price , 0 ) )

5

That will raise a warning (because of the NULL) I think? and, in my experience :frowning:, those warning can play havoc with APPs ...

MIN ( COALESCE( NullIf( price , 0 ) , 99999999999 )

maybe?

6 
SELECT MIN(Price)
FROM MyTable
WHERE Price <> 0

would be better, but presumably the MIN(Price) is in a query doing other things, rather than just getting the price?

7

I ca't do a where because i'm also getting max(price ) and i want the max price to count even though with a -

I will try the nullif

8

How about this:

select min(case when price=0 then 9999999999.99 else price end)
      ,max(price)
  from yourtable
9

I guess the real question would be... why do you have a price of zero in the price column at all? That's like having NULL in the column. If you don't know the price, the row shouldn't exist.

10

it's sale price and if it's 0 then the item is not on sale

11

personally I would store that "fact" some other way, rather than overloading the Price column with prices that have special meaning - otherwise feels like the whole Y2K problem of using "99" as a bogus year to indicate "Not applicable" ...

Either a separate IsNotForSale column, or I think slightly less bad would be to have Price = NULL

We definitely have things that can be "bought" which have a price of zero - e.g. a catalogue, or fact sheet to accompany a product, or similar

12

I've has the same issue and, because the database was created and maintained by a third party, I had to go about it in this way:(even though it isn't optimal)

Select IT.ItemId, Mn.MinPrice, Mx.MaxPrice
From ItemTable IT
LEFT JOIN
(
Select ItemId, Max(Price) As MaxPrice
FROM ItemTable
)Mx ON IT.ItemId = Mx.ItemId

LEFT JOIN
(
Select ItemId, Min(Price) As MinPrice
From ItemTable
WHERE Price <> 0
)Mn ON IT.ItemId = Mn.ItemId