0

Here is my tables

enter image description here

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

enter image description here

2 Answers 2

0
SELECT TOP(3) --WITH TIES 
    h.vendor
    ,MIN(d.Rate) as Rate
FROM #Header as h
    inner join #Detail as d
        on  H.HNo = D.DNo
GROUP BY h.vendor
order by RATE

or option 2

SELECT TOP(3)
    a.vendor
    ,a.RATE
FROM
(
    SELECT
        h.vendor
        ,d.RATE
        ,ROW_NUMBER() OVER(PARTITION BY h.Vendor ORDER BY d.Rate ASC) as RN
    FROM #Header as h
        inner join #Detail as d
            on  H.HNo = D.DNo
)a
WHERE a.rn = 1
ORDER BY a.RATE ASC

output:

vendor  RATE
V1  10.00
V4  20.00
V3  30.00
0

Here's simple query that returns desired result.

SELECT TOP 3 * FROM
(
SELECT vendor,MIN(RATE) RATE FROM #Header,#Detail
where DNo = HNo
GROUP BY
vendor
) T
ORDER BY RATE ASC

Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.