CODEWITHSUNDEEP
mysqlsqldatabase
Luke Smith
Luke Smith

Reputation: 39

Select only rows with matching pair

I have a MySQL table as below

ID  SHEET_NUMBER    DAYS RESULT
1   55201           9   10
2   55209           28  25.5
3   55209           28  27.9
4   38558           7   12
5   38552           5   19
6   38559           5   5

I want to select only rows with firstly matching sheet numbers & only if there if there is a matching pair of 28 days specimens

so if there is only one 28 day it will select nothing but if there is at least 2x 28 day it will get both rows

I'm totally lost, i know i should be using group by.. but i'm unsure of its use.

thankyou

Upvotes: 1

Views: 117

Answers (2)

spencer7593
spencer7593

Reputation: 108410

First, write a query that finds sheet_number with two or more rows with days value of 28.

  SELECT d.sheet_number
    FROM my_table_below d
   WHERE d.days = 28
   GROUP BY d.sheet_number
  HAVING COUNT(1) > 1

With that query, we can use that as an inline view, and join back to the original table to find the matching rows:

SELECT t.*
  FROM ( SELECT d.sheet_number
           FROM my_table_below d
          WHERE d.days = 28
          GROUP BY d.sheet_number
         HAVING COUNT(1) > 1
       ) m
  JOIN my_table_below t
    ON t.sheet_number = m.sheet_number
   AND t.days = 28 
 ORDER BY t.sheet_number, t.id

Omit the condition t.days = 28 on the outer query, if the specification is to return all of the rows for the sheet_number, not just the rows with days=28. (The specification is a bit unclear.)

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30819

Can you try the following query:

SELECT * 
FROM test
WHERE sheet_number IN (
    SELECT sheet_number
    FROM test
    WHERE days = 28
    GROUP BY sheet_number
    HAVING COUNT(*) >= 2
);

Here's the SQL Fiddle.

Upvotes: 3

Related Questions