We help IT Professionals succeed at work.

query lowest costs from table

on
Hi,

I have one table "tblCosts" of Ups and Fedex costs and would like to find the lowest TotalCost per RateId and also show the associated Carrier.

CostId    RateId    Carrier    TotalCost
1               12          FedEx       20
2               12          Ups          25
3               17          FedEx       50
4               17          Ups          48

Result.
CostId    RateId    Carrier    TotalCost
1              12          FedEx         20
4              17          Ups            48

Thanks
Comment
Watch Question

View Solution Only

CERTIFIED EXPERT

Commented:
1. Get the following result first..

SELECT min(TotalCost) as Cost , RateId from tblCosts group by RateId

RateId    Cost
12             20
17             48

2. And then.. (Syntax)

Select * from tblCosts where...  (SELECT min(TotalCost) as Cost, RateId from tblCosts group by RateId)....

* Try to use something like ... I have no database now, and no further testing

Commented:
create table tblCosts
(
CostId number primary key,
RateId number,
Carrier varchar2(20),
TotalCost number
);

insert into tblCosts
values(1,12,'FedEx',20);

insert into tblCosts
values(2,12,'Ups',25);

insert into tblCosts
values(3,17,'FedEx',50);

insert into tblCosts
values(4,17,'Ups',48);

Query-----------------------------------------------------------------------------------------------------
select * from tblCosts
where TotalCost in
(
select min(TotalCost)
from tblCosts
group by RateId
);

output--
COSTID    RATEID    CARRIER         TOTALCOST
---------------------------------------------------------------
1               12            FedEx                     20
4              17             Ups                         48

Query---------------------------------------------------------------------------------------------------------

select * from tblCosts
where TotalCost in
(
select min(TotalCost)
from tblCosts
group by Carrier
);

output--
COSTID    RATEID    CARRIER         TOTALCOST
---------------------------------------------------------------
1                12            FedEx                     20
2                12             Ups                        25
Information Technology Specialist
CERTIFIED EXPERT
Commented:
select t.* from tblcosts,
(select rateid, min(totalcost) as cost from tblcosts group by rateid) x
where t.rateid = x.rateid and t.totalcost = x.cost;

Commented:
awking00

Getting the error..

The Microsoft Office Database engine  does not recognize "t.*" as a valid field name or expression.

Commented:
Nearly there...a few to many columns in the query result but still good,

select * from tblcosts AS T1,
(select rateid, min(totalcost) as cost from tblcosts group by rateid) x
where t1.rateid = x.rateid and t1.totalcost = x.cost;

Commented:
Thanks
Information Technology Specialist
CERTIFIED EXPERT

Commented:
Sorry I forgot the t alias originally.

Commented:
I want to reopen this question beacuse I am not satisfied with distribution of points.