Here is my tables
I want to select Top 3 lowest quoted rate vendors without duplicate Entry. Expected output will be V1
, V4
, V3
without using while loop. is this possible?
Table details
CREATE TABLE #Header(id int identity,HNo varchar(10),vendor varchar(50))
insert into #Header values('H1','V1')
insert into #Header values('H2','V2')
insert into #Header values('H3','V3')
insert into #Header values('H4','V4')
insert into #Header values('H5','V1')
insert into #Header values('H6','V6')
insert into #Header values('H7','V4')
CREATE TABLE #Detail(id int identity,DNo varchar(10),RATE MONEY )
insert into #Detail values('H1', 10)
insert into #Detail values('H2', 40)
insert into #Detail values('H3', 30)
insert into #Detail values('H4', 20)
insert into #Detail values('H5', 20)
insert into #Detail values('H6', 60)
insert into #Detail values('H7', 70)
SELECT * FROM #Header
SELECT * FROM #Detail
I have the result like below but vendors are duplicated, i can achieve using while loop and check eveytime vendor exist or not. is this any other way?
SELECT top 3 RATE,vendor FROM #Header,#Detail
where DNo = HNo
order by RATE