| Author |
 Topic  |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
 Posted - 02/20/2013 : 15:07:52
|
i have a query
select percentoffminimum,freeshippingminimum,amountoffminimum from coupons
now for each of these the value can be Null, 0 or with a value
I need to query if any of these are not null or 0 -- I want to get the highest minimum
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/20/2013 : 15:15:52
|
See if one of the 3 queries in the below example will work for you:CREATE TABLE #tmp (id INT);
INSERT INTO #tmp VALUES (1),(2),(NULL),(0);
SELECT MIN(id) FROM #tmp; -- gives 0
SELECT MAX(id) FROM #tmp; -- gives 2
SELECT MIN(id) FROM #tmp WHERE id <> 0; -- gives 1
DROP TABLE #tmp;
|
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3829 Posts |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 02/20/2013 : 16:08:27
|
I want the max of the 3 fields -- not max of 1 field
would that be with a union?
heres sample data
percentoffminimum freeshippingminimum amountoffminimum
NULL NULL NULL
NULL NULL 50.00
NULL 50.00 NULL
NULL NULL NULL
0.00 NULL 25.00
from the above the first should return 0 (all nulls), second 50, third 50,4th 0 , 5th 25
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 02/20/2013 : 16:25:50
|
This??? Not Sure
Select MAX(A)
from
(
Select percentoffminimum A from Coupons
union
Select freeshippingminimum from Coupons
union
Select amountoffminimum from Coupons
)P |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3829 Posts |
Posted - 02/20/2013 : 17:37:05
|
Esthera, since you are a "Flowing Fount of Yak Knowledge," I'd assume you know how to post data in a consumable format. Either my assumption is incorrect or you are trying to make it hard for us to help you. So, please read the links I posted above on how to ask your questions on a SQL forum.
Be that as it may, here is a solution for ya:
DECLARE @Foo TABLE (percentoffminimum NUMERIC(18,4), freeshippingminimum NUMERIC(18,4), amountoffminimum NUMERIC(18,4))
INSERT @Foo
VALUES
(NULL, NULL, NULL),
(NULL, NULL, 50.00),
(NULL, 50.00, NULL),
(NULL, NULL, NULL),
(0.00, NULL, 25.00)
SELECT
(
SELECT COALESCE(MAX(P), 0.00)
FROM
(
VALUES
(percentoffminimum),
(freeshippingminimum),
(amountoffminimum)
) AS Foo(P)
) AS SomeUnspecifiedColumnName
FROM
@Foo AS F |
Edited by - Lamprey on 02/20/2013 17:44:55 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 02/20/2013 : 23:45:59
|
May this this???
DECLARE @tab TABLE( percentoffminimum DEC(5,2), freeshippingminimum DEC(5,2), amountoffminimum DEC(5,2))
INSERT INTO @tab
SELECT NULL, NULL, NULL union all
SELECT NULL, NULL, 50.00 union all
SELECT NULL, 50.00, NULL union all
SELECT NULL, NULL, NULL union all
SELECT 0.00, NULL, 25.00
--from the above the first should return 0 (all nulls), second 50, third 50,4th 0 , 5th 25
SELECT COALESCE( NULLIF(percentoffminimum, 0), NULLIF(freeshippingminimum, 0), NULLIF(amountoffminimum, 0), 0)
FROM @tab
EDIT : Don't mind this post.. I have posted this by seeing the explanation only(from the above the first should return 0 (all nulls), second 50, third 50,4th 0 , 5th 25 -- Posted by Esthera at 02/20/2013 : 16:08:27).. I think Lamprey's post is working fine as per your need
--
Chandu |
Edited by - bandi on 02/21/2013 00:18:55 |
|
|
| |
Topic |
|
|
|
best way to get amount from nulls
