I have got a following table named "Details":

I would like to find/select all store number records where value of reference_number 48 is bigger than value of reference_number 54, so I would like 222223 and 222224 to be returned from my query.

I feel like it should be done with HAVING clause, but I'm not quite sure how to use it in this case. Problably also joining the same table may be advised to use in select query.

CodePudding user response：

Yes you can use HAVING for this

SELECT Shop_number
FROM   Details
GROUP  BY Shop_number
HAVING MAX(CASE WHEN Reference_number = 48 THEN Value END) > 
       MAX(CASE WHEN Reference_number = 54 THEN Value END) 

CodePudding user response：

Another approach, as you mentioned with self-join:

SELECT a.Shop_number, a.Value AS Value48, b.Value as Value54
FROM details a
INNER JOIN details b 
  ON a.Shop_number = b.Shop_number 
   AND a.Reference_number = 48 
   AND b.Reference_number = 54
WHERE a.Value > b.Value

CodePudding user response：

Use a pivot aggregation approach:

SELECT Shop_number
FROM Details
GROUP BY Shop_number
HAVING MAX(CASE WHEN Reference_number = 48 THEN Value END) >
       MAX(CASE WHEN Reference_number = 54 THEN Value END);

CodePudding user response：

Or you can just check that other value via subquery:

SELECT DISTINCT Shop_number
FROM Details d1
WHERE Reference_number=48
  AND Value > (
    select top 1 Value 
    from Details 
    where Shop_number=d1.Shop_number
      and Reference_number=54
  )