0

I have this table:

auctionid   bid     bidtime     bidder      bidderrate  openbid price
8211851222  55      6.021053    smoothy43m  69          50      161
8211851222  51      6.378843    ahmadf316   1           50      161
8211851222  54      6.379225    ahmadf316   1           50      161
8211851222  59.66   6.499745    dang800     5           50      161
8211851222  57      6.654282    txrang10    98          50      161
8211851222  60      6.654583    txrang10    98          50      161
8211851222  65      6.713646    em19680     23          50      161
8211851222  65      6.734225    justin      10          50      161
8211851222  80      6.736042    losiewiczp  1683        50      161
8211851222  67      6.772292    smoothy43m  69          50      161
8211851222  69.01   6.782442    east        8           50      161
8211851222  71.01   6.792025    sammy       4           50      161
8211851222  73.01   6.792141    sammy       4           50      161
8211851222  80      6.801331    em19680     23          50      161
8211851222  85      6.865845    ekhcp       0           50      161
8211851222  100     6.878935    jareem04    0           50      161
8211851222  90      6.908819    wet444we    12          50      161
8211851222  99      6.908993    wet444we    12          50      161
8211851222  120     6.909167    wet444we    12          50      161
8211851222  115     6.937176    ward        29          50      161
8211851222  140     6.942627    jardo02     12          50      161
8211851222  141     6.951192    jardo02     12          50      161
8211851222  130     6.968461    wet444we    12          50      161
8211851222  140     6.968715    wet444we    12          50      161
8211851222  150     6.968912    wet444we    12          50      161
8211851222  150     6.979595    jardo02     12          50      161
8211851222  160     6.979699    jardo02     12          50      161
8211851222  161     6.979896    ojigun      238         50      161

I want the output of the bidder name who has invested lowest bidderrate

I want the output like this:

bidder    bidderrate
ekhcp     0
jareem04  0

I have written this query but getting error.

select auctionid, bidder, bidderrate 
from [Product].[dbo].[Ebay] 
where auctionid = 8211851222 
group by bidderrate,auctionid,bidder
having bidderrate = min(bidderrate);

Is it possible to get my answer without using any nested query?

  • 4
    What's the problem with a subquery? – sticky bit Jul 25 '19 at 15:00
5

It seems that you want multiple bidders to be returned if there are more than one with the lowest bidderrate.

In that case, you could use TOP with TIES

select TOP(1) WITH TIES  auctionid, bidder, bidderrate 
FROM [dbo].[Ebay] 
WHERE auctionid = 8211851222 
ORDER BY bidderrate asc;

DB<>Fiddle based on your example.

|improve this answer|||||
  • Thanks. But if I dont want multiple bidder name then is it possible to use min function? – Bhaskar Das Jul 25 '19 at 15:06
  • 2
    If you dont want multiple bidders (a random one) then remove the top with ties and/or order on more columns. – Randi Vertongen Jul 25 '19 at 15:41
1

You can first define your data set as a ranked set and then limit results to the top ranked value(s).

WITH bidderrateRNK AS
(
  SELECT auctionid
  , bidder
  , bidderrate 
  , RANK() OVER(ORDER BY bidderrate ASC) AS Ranks
FROM [dbo].[Ebay]
WHERE auctionid = 8211851222 
)

SELECT *
FROM bidderrateRNK
WHERE Ranks =1 

By the way, it is very interesting to see the different answers people have to the problem of avoiding sub-queries. Thanks for asking!

|improve this answer|||||
  • Edited 6th line from: OVER(ORDER BY bidderrate ASC) AS Ranks to: RANK() OVER(ORDER BY bidderrate ASC) AS Ranks – McDonalds Happy Meal Jul 25 '19 at 15:45
1

Something like this?

PRINT 'Note: Previous Statement Ends in Semicolon';    
WITH CTE_BIDDER_DATA AS 
        (SELECT DISTINCT auctionid, bidder FROM [Product].[dbo].[Ebay])
        SELECT 
          cte.auctionid,
          cte.bidder,  
          oa.bidderrate 
        FROM CTE_BIDDER_DATA cte
        OUTER APPLY (SELECT TOP 1 bidderrate from [Product].[dbo].[Ebay] 
        WHERE bidder = cte.bidder 
        ORDER BY bidderrate ) oa
        WHERE cte.auctionid = 8211851222;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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