Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This is my Query:
select min(t2.Price) as Price, t2.RuleSet, t1.BoardID, t1.Grading from [dbo].[tblLimitingRules] as t1
left join [dbo].[tblProducts] as t2 on
t1.RuleSetID=t2.RuleSet
where
t2.RuleSet <> ''
and t1.Grading = 1 and
t1.BoardID ='19060205'
group by t1.BoardID, t1.Grading, t2.RuleSet
order by t1.BoardID asc
This is my result:
Price RuleSet BoardID Grading
1900.00 644 19060205 1
2500.00 691 19060205 1
1475.00 692 19060205 1
I just want to keep the row With the lowest Price on all BoardID, this is just one of ~ 18 million BoardID and they have different RuleSet and Price.
Is there any way in sql or qlikview to solve this?
You're right.
Then
Temp:
LOAD *,
BoardID & '+' & Price as key.
select min(t2.Price) as Price, t1.BoardID from [dbo].[tblLimitingRules] as t1
left join [dbo].[tblProducts] as t2 on
t1.RuleSetID=t2.RuleSet
where
t2.RuleSet <> ''
and t1.Grading = 1 and
t1.BoardID ='19060205'
group by t1.BoardID,
order by t1.BoardID asc
Table:
LOAD *,
BoardID & '+' & Price as key.
Where Exists(key)
select min(t2.Price) as Price, t2.RuleSet, t1.BoardID, t1.Grading from [dbo].[tblLimitingRules] as t1
left join [dbo].[tblProducts] as t2 on
t1.RuleSetID=t2.RuleSet
where
t2.RuleSet <> ''
and t1.Grading = 1 and
t1.BoardID ='19060205'
group by t1.BoardID, t1.Grading, t2.RuleSet
order by t1.BoardID asc
Drop Table Temp;
Try this
select min(t2.Price) as Price, t1.BoardID from [dbo].[tblLimitingRules] as t1
left join [dbo].[tblProducts] as t2 on
t1.RuleSetID=t2.RuleSet
where
t2.RuleSet <> ''
and t1.Grading = 1 and
t1.BoardID ='19060205'
group by t1.BoardID,
order by t1.BoardID asc
Then you need a new left join to T1 with Price and BoardID to get RuleSet and Grading
Hope this helps
That will not fix the problem, Left join on BoardID will still gives me Three rows here, Price is not a Field from T1.
Grading is not really important here, it is to get correct RuleSet so I can use that as a New key to other table.
You're right.
Then
Temp:
LOAD *,
BoardID & '+' & Price as key.
select min(t2.Price) as Price, t1.BoardID from [dbo].[tblLimitingRules] as t1
left join [dbo].[tblProducts] as t2 on
t1.RuleSetID=t2.RuleSet
where
t2.RuleSet <> ''
and t1.Grading = 1 and
t1.BoardID ='19060205'
group by t1.BoardID,
order by t1.BoardID asc
Table:
LOAD *,
BoardID & '+' & Price as key.
Where Exists(key)
select min(t2.Price) as Price, t2.RuleSet, t1.BoardID, t1.Grading from [dbo].[tblLimitingRules] as t1
left join [dbo].[tblProducts] as t2 on
t1.RuleSetID=t2.RuleSet
where
t2.RuleSet <> ''
and t1.Grading = 1 and
t1.BoardID ='19060205'
group by t1.BoardID, t1.Grading, t2.RuleSet
order by t1.BoardID asc
Drop Table Temp;
Hi,
yes this point me into the correct direction, some small adjustment fix the problem, thanks.
Hi Staffan
You're welcome.
Until next time
Joaquín