January 10, 2007 at 11:36 am
We have a table that looks like :
Part, QtyBreak, Price
p1, 100, 5
p1, 200, 3
p1, 1000, 1
p2, 150, 10
p2, 1000, 5
p2, 2000, 1
now we need to find out the price for the MIN QtyBreak of a part.
for the data above, the results should be
p1, 100, 5
p2, 150, 10
Can a query do the trick?
thanks.
January 10, 2007 at 12:00 pm
I am going to make one assumption: Price doesn't have null and Price gets lower as the QtyBreak gets lower:
Select Part, min(QtyBreak) as QtyBreak, min(Price) as Price
from tble
group by part
Russel Loski, MCSE Business Intelligence, Data Platform
January 10, 2007 at 12:03 pm
The more complicated:
select a.Part, a.QtyBreak, a.Price from tbl a
inner join (Select Part, min(QtyBreak) as QtyBreak from tbl
group by Part) b
on a.Part = b.Part and a.QtyBreak = b.QtyBreak
Russel Loski, MCSE Business Intelligence, Data Platform
January 10, 2007 at 1:15 pm
The following is based on the original post, give it a try:
create table dbo.Part (
PartId char(2),
QtyBreak smallint,
Price money
)
go
insert into dbo.Part (PartId, QtyBreak, Price) values ('p1',100, 5.00)
insert into dbo.Part (PartId, QtyBreak, Price) values ('p1',200, 3.00)
insert into dbo.Part (PartId, QtyBreak, Price) values ('p1',1000, 1.00)
insert into dbo.Part (PartId, QtyBreak, Price) values ('p2',150, 10.00)
insert into dbo.Part (PartId, QtyBreak, Price) values ('p2',1000, 5.00)
insert into dbo.Part (PartId, QtyBreak, Price) values ('p2',2000, 1.00)
go
select * from dbo.Part
go
select
part.PartId,
part.QtyBreak,
part.Price
from
dbo.Part part
inner join (
select
p.PartId,
min(p.QtyBreak) as QtyBreak
from
dbo.Part p
group by
p.PartId
) prt
on (part.PartId = prt.PartId
and part.QtyBreak = prt.QtyBreak)
go
drop table dbo.Part
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply