SQLTeam.com | Weblogs | Forums

Select min value not including 0

tsql

#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