0

There is a group of rows which have the losscode and service odom. The contractnumber and service odometer are used to group the rows.

Please, may you help me to find the way to select only the row group which have the slosscode='REVSTD'?

enter image description here

2
  • What does grouping mean if you only want one row? The following returns the row: SELECT sContractNumber, sLossCode, iServiceOdom WHERE sLossCode='REVSTD'
    – danblack
    Sep 18, 2018 at 22:38
  • 1
    @Maricruz If you're going to access the site from different browsers or devices, an unregistered (cookie-based) account isn't going to work for you. Consider registering instead, and then merging your accounts. That way you'll retain reputation earned, be able to edit your question without peer review, and accept an answer.
    – Paul White
    Sep 19, 2018 at 13:12

1 Answer 1

1

I assume you want all rows of the group(s) where at least one row of the group has sLossCode = 'REVSTD'. In this case, you don't need GROUP BY. You can use either a self join (but that would only work under certain UNIQUE constraint requirements) or an EXISTS subquery:

SELECT s.sContractNumber, s.sLossCode, s.iServiceOdom
FROM service_table AS s
WHERE EXISTS
      ( SELECT 1
        FROM service_table AS q
        WHERE q.sLossCode = 'REVSTD'
          AND q.sContractNumber = s.sContractNumber
          AND q.iServiceOdom = s.iServiceOdom
      ) ;

Your Answer

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

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