Keep one row

[stabben23]

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?

[JoaquinLazaro]

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

[stabben23]

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.

[JoaquinLazaro · Accepted solution]

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;

[stabben23]

Hi,

yes this point me into the correct direction, some small adjustment fix the problem, thanks.

[JoaquinLazaro]

Hi Staffan

You're welcome.

Until next time

Joaquín