0

The idea is to get a table with positions and best offers for them with the request no that made that offer

This is my code below

SELECT PosId   = BidPositionCurrentState.Id
     , PosNo = BidPositionCurrentState.No
     , PosName = BidPositionCurrentState.Name
     , RequestNo = r.No
     , offerprice = bop.offerprice
     , offertime = bop.ActDate
FROM BidPosition bp WITH (NOLOCK)
INNER JOIN BidPositionCurrentState(NOLOCK) ON BidPositionCurrentState.Id = bp.Id
INNER JOIN BidOfferPosition bop (NOLOCK) ON bp.id = bop.BidPositionId
INNER JOIN Request r WITH (NOLOCK) ON r.Id = bop.RequestId AND r.StatusId IN (5, 7)
WHERE bp.BidId = @Parm AND and bop.OfferPrice IN (SELECT CASE WHEN Bid.IsIncrease = 0
                                       THEN min(bop.OfferPrice)
                                                              else max(bop.OfferPrice) end 
                                                  from BidOfferPosition bop (NOLOCK)
                                                  where bop.BidPositionId = bp.Id)
ORDER BY PosId

I've managed to get the best offers, but when two or more requests have the the same best offer I, of course, get this output:

PosNo PosName BestOffer RequestNo OfferDate
1 pen 10 1 11.04.2023 12:50
1 pen 10 3 11.04.2023 12:57
2 highlighter 6 2 10.04.2023 11:30
3 eraser 7 1 11.04.2023 09:32
3 eraser 7 2 11.04.2023 10:45

The disired output would be (select request that made the offer first):

PosNo PosName BestOffer RequestNo OfferDate
1 pen 10 1 11.04.2023 12:50
2 highlighter 6 2 10.04.2023 11:30
3 eraser 7 1 11.04.2023 09:32

I've tried distincts and subqueries with top 1 in RequestNo but nothing worked

nicolechant
  • 1
  • 2
    [Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) If you "must" (you don't) use `NOLOCK` against every object, why are you not changing the isolation level? You're also using a deprecated method of using table hints. – Thom A Apr 11 '23 at 09:40
  • 1
    `NOLOCK` doesn't mean `take no locks` or `go fast`, it means *take table-level locks while returning dirty and duplicate data, while throwing at random*. You won't fix missing indexes or bad queries with `NOLOCK` – Panagiotis Kanavos Apr 11 '23 at 09:42
  • @PanagiotisKanavos I don't think it actually takes table-level `S` locks. It does take schema `Sch-S` locks, and the rest of your comment is very true. – Charlieface Apr 11 '23 at 10:10

0 Answers0