September 14, 2011 at 5:15 pm
This should be pretty simple but I am really new at this so I am having some issues with getting the result that I need.
I have three columns of data in the same table
MasterID
SubID
SubID Price
I am trying to run a query that will output data for repeating MasterID with lowest price.
Example:
Master ID|SubID|SubdID Price
1234 aa $1.00
1235 cc $4.00
1234 bb $0.50
The result that I am looking for is
Master ID|SubID|SubdID Price
1234 bb $0.50
because the MasterID has multiple SubID and SubID bb has a lower price than SubID aa.
Thank you so much for your help this will help me out a lot.
September 14, 2011 at 6:47 pm
This article may help you
http://www.sqlservercentral.com/articles/T-SQL/70807/
Sorry I can not post actual code since the computer with my Sandbox DB is out for repairs .. but hope the link will get your started.
September 14, 2011 at 7:14 pm
Do you want the lowest price for each unique MasterID (in which case your output should include 1235 cc 4) or do you want the lowest price across the whole table?
Or am I missing the point?
September 14, 2011 at 7:28 pm
I am looking for the lowest price for each MasterID with more than one SubID. So if the MasterID has only one SubID then that record should be ignored.
Thank you for your help,
David
September 14, 2011 at 8:08 pm
But how do we get Sub_ID in the select list?
September 14, 2011 at 9:46 pm
this?
; with cte as
(
SELECT master_id, sub_id , slave_price , ROW_NUMBER() over( partition by master_id , sub_id order by slave_price asc) AS slave_price_min
FROM SlaveMarket
)
select * from cte
where slave_price_min = 1
September 14, 2011 at 9:48 pm
or this?
; with cte as
(
SELECT distinct master_id, sub_id
FROM SlaveMarket
)
select * , outp.minval
from cte a
cross apply ( select MIN ( sales_price ) minval
from SlaveMarket b
where a.master_id = b.master_id
and a.sub_id = b.sub_id ) outp
September 15, 2011 at 1:13 am
Thank you, how would we get the SubID in there?
CELKO (9/14/2011)
david 98157 (9/14/2011)
I am looking for the lowest price for each master_id with more than one sub_id. So if the master_id has only one sub_id then that record [sic] should be ignored.In the future follow the rules and post DDL, not narrative. Rows are not records. I had to have some fun with your data element names 😀
CREATE TABLE SlaveMarket
(master_id CHAR(2) NOT NULL,
sub_id CHAR(2) NOT NULL,
PRIMARY KEY (master_id, sub_id),
slave_price DECIMAL (8,2) NOT NULL
CHECK (slave_price> 0.00));
SELECT master_id, MIN(slave_price) AS slave_price_min
FROM SlaveMarket
GROUP BY master_id
HAVING COUNT(*) > 1;
September 15, 2011 at 3:58 am
Another Way:-
select a1.* from a as a1, a as a2
where a1.master_id = a2.master_id
and a1.subid = a2.subid
and a2.subdid_price = (select min(subdid_price) from a)
OR
select b.* from a as b,
(select master_id, min(subdid_price) as min
from a
group by master_id
having count(*) > 1) as c
where b.master_id = c.master_id
and min = subdid_price
Regards
Palash Gorai
September 16, 2011 at 4:55 am
declare @t table(
MasterID Int,
SubID varchar(10),
[SubID Price] decimal(5,2)
)
insert into @t values
(1234, 'aa', 1.00),
(1235, 'cc', 4.00),
(1234, 'bb', 0.50)
; with cte
As (
select
t.MasterID
, min(t.[SubID Price]) mp
from
@t t
group by
t.MasterID
having
count(*) > 1)
select
t.MasterID
, t.SubID
, t.[SubID Price]
from
@t t
inner join cte c on c.MasterID = t.MasterID And c.mp = t.[SubID Price]
I Have Nine Lives You Have One Only
THINK!
September 16, 2011 at 12:23 pm
Here's another way to do it. I find that some people forget that COUNT and SUM are also windowing functions and can be used in a partition.
declare @t table(
MasterID Int,
SubID varchar(10),
SubIDPrice decimal(5,2)
)
insert into @t values
(1234, 'aa', 1.00),
(1235, 'cc', 4.00),
(1234, 'bb', 0.50)
; WITH CTEMin AS
( SELECT MasterID
, SubID, SubIDPrice
, ROW_NUMBER() OVER (PARTITION BY MasterID ORDER BY SubIDPrice ASC) AS RowNum
, COUNT(*) OVER (PARTITION BY MasterID) AS MasterIDCount
FROM @T
)
SELECT MasterID, SubID, SubIDPrice
FROM CTEMin
WHERE MasterIDCount > 1
AND RowNum = 1
Todd Fifield
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply