October 14, 2002 at 12:18 am
I was hoping someone could help on this one...
Here is my statement:
select * from test_td a, test_im b
where a.MfgPartNbr=1 and b.Mfgpartnbr=1
Table structure is this:
test_td = col_id, MfgPartNbr, Cost
test_im = col_id, MfgPartNbr, Cost
I want to compare the value of cost and return the lowest value.
in other words:
select * from test_td a, test_im b
where a.MfgPartNbr=1 and b.Mfgpartnbr=1
and cost=lowest_value
Thanks in advance.
Andrew
Andrew
http://eshopsoho.com
October 14, 2002 at 12:35 am
Hi
I don't know if I have understood you correctly. But my understanding is that you have two tables with the same kind of Mfgpartnumber and cost values.And you need the minimum cost for each Mfgpartnumber.
If this is the case, then the following query will work.
select MfgPartNbr, min(cost) from (select * from test_td
union
select * from test_tm) x
group by MfgPartNbr
If not, please reply back.
Thanks,
Anbu
October 14, 2002 at 12:49 am
Anbu,
AWESOME!!! THAT Works great. I see you can use max(cost) as well. Are there other arguments?
Also, what does the 'x' before group mean?
Thanks Again,
Andrew
Andrew
http://eshopsoho.com
October 14, 2002 at 12:58 am
smfiber,
If you join it as you, for each record in one table you'll get all the records in the other table. Although the where clause will cut it down it's not a good way to go. Now if there's only one record for each mfgPartNbr in each table then you could do this.
select
MfgPartNbr,
Case
when (a.Cost - b.Cost) < 0 then b.Cost
else a.cost
end
from
test_td a
inner join
test_im b
on a.MfgPartNbr = b.Mfgpartnbr
where mfgPartNbr = <value>
If there's more than one record then either do a union between the two tables as a sub query and then select the max value.
Alternatively do a select max etc subquery for each table and join on this. But this and the above query won't work if there's a chance of there been no records for that part number in either table as an outer join would be required and you don't which table to depend on.
You should have a table that has the MfgPartNbr as a unique record. This can be joined to each of the tables as an outer join to ensure you have always have a record and then compare on this (using coalesce to avoid nulls)
Without knowing the structure in detail it's a bit hard to tell the best way but I hope this helps.
Cheers,
Mike.
October 14, 2002 at 1:09 am
x is an imaginary table name, that can be anything. You can use all the aggregare functions there. The concept is very simple. I am creating an imaginary table x, which is actually the output of the query that I have there. And then, I query the table x.
October 14, 2002 at 4:42 am
How about something like this...
SELECT
TD.col_id
,TM.colid
,CASE WHEN ISNULL(TD.Cost, 999999) < ISNULL(TN.Cost, 999999) THEN TD.Cost ELSE TM.Cost END AS 'Lowest Cost'
FROM
test_td AS TD
FULL OUTER JOIN test_im AS IM ON IM.MfgPartNbr = TD.MfgPartNbr
October 14, 2002 at 4:45 am
Woops, that second ISNULL should be zero - assuming you don't have any negative costs.
October 14, 2002 at 11:07 pm
Thanks Julian - I'd never noticed the Full Outer Join before.
And with that knowledge in hand Julians is definately the way to go.
cheers,
Mike
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply