Solved

query lowest costs from table

Posted on 2012-03-12
8
260 Views
Last Modified: 2012-03-13
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
0
Comment
Question by:ExpressMan1
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 13

Expert Comment

by:rhinoceros
ID: 37712851
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
0
 
LVL 1

Expert Comment

by:ishanjrana
ID: 37713203
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
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 37715132
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;
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:ExpressMan1
ID: 37715263
awking00

Getting the error..

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

Author Comment

by:ExpressMan1
ID: 37715352
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;
0
 

Author Closing Comment

by:ExpressMan1
ID: 37715574
Thanks
0
 
LVL 32

Expert Comment

by:awking00
ID: 37716111
Sorry I forgot the t alias originally.
0
 
LVL 1

Expert Comment

by:ishanjrana
ID: 37716288
I want to reopen this question beacuse I am not satisfied with distribution of points.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question