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)
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
MIN ( NULLIF ( price , 0 ) )
That will raise a warning (because of the NULL) I think? and, in my experience , those warning can play havoc with APPs ...
MIN ( COALESCE( NullIf( price , 0 ) , 99999999999 )
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?
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
How about this:
select min(case when price=0 then 9999999999.99 else price end) ,max(price) from yourtable
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.
it's sale price and if it's 0 then the item is not on sale
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
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
Select ItemId, Max(Price) As MaxPrice
)Mx ON IT.ItemId = Mx.ItemId
Select ItemId, Min(Price) As MinPrice
WHERE Price <> 0
)Mn ON IT.ItemId = Mn.ItemId