CODEWITHSUNDEEP
sqloracle-database
jaykio77
jaykio77

Reputation: 391

Finding Rows based on a set of same columns in ORACLE

Consider following table:-

-----------------------------------------
  ID      TEACHER    SUBJECT     GRADE
-----------------------------------------
  1        Jack       Physics      8
  2        Paul       Chemistry    10
  3        Susan      English      6
  4        Jack       Maths        8
  5        Jack       Physics     10
  6        Paul       Chemistry   10

i want to identify the rows with same values for TEACHER and SUBJECT columns. irrespective of values in ID and GRADE columns. The following rows thus will be identified:-

 -------------------------------------
  ID      TEACHER     SUBJECT   GRADE
 -------------------------------------
  1        Jack       Physics      8
  2        Paul       Chemistry    10
  5        Jack       Physics     10
  6        Paul       Chemistry   10
 -------------------------------------

how to achieve such a result in oracle sql.

Upvotes: 0

Views: 57

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35623

Using ROW_NUMBER() will provide a simple method of choosing just one row for the unique combinations of teacher and subject but I haven't exactly matched the results provided by the question. However using ROW_NUMBER() enables a logic where you can choose which other field(s) will determine which of the rows is returned:

SELECT 
       id, teacher, subject, grade 
FROM (
      SELECT
           id, teacher, subject, grade 
         , row_number() over(partition by teacher, subject ORDER BY id ASC) as RN
      FROM Table1
      )
WHERE rn = 1
ORDER BY teacher, subject ;

Using ascending order of ID the result is:

| ID | TEACHER |   SUBJECT | GRADE |
|----|---------|-----------|-------|
|  4 |    Jack |     Maths |     8 |
|  1 |    Jack |   Physics |     8 |
|  2 |    Paul | Chemistry |    10 |
|  3 |   Susan |   English |     6 |

or simply by changing the order to: grade DESC

| ID | TEACHER |   SUBJECT | GRADE |
|----|---------|-----------|-------|
|  4 |    Jack |     Maths |     8 |
|  5 |    Jack |   Physics |    10 |
|  2 |    Paul | Chemistry |    10 |
|  3 |   Susan |   English |     6 |

Often this technique is used to get the most recent or oldest rows (based on some date/time data) for another example of how it can be used.

SQLfiddle demo

Upvotes: 0

krokodilko
krokodilko

Reputation: 36137

SELECT * FROM table1 t1
WHERE EXISTS (
  SELECT null FROM Table1 t2
  WHERE t1."TEACHER" = t2."TEACHER"
    AND t1."SUBJECT" = t2."SUBJECT"
    AND t1."ID" <> t2."ID"
)
ORDER BY "ID"

demo: http://sqlfiddle.com/#!4/3d2bd4/3

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

You can use the below select:

SELECT 
      a.* 
  FROM (SELECT 
             COUNT(1) cnt,
             teacher,
             subject 
         FROM your_table
         GROUP BY teacher,subject
         HAVING COUNT(1) > 1)rec,
Your_table a
WHERE a.teacher=rec.teacher AND a.subject=rec.subject

Upvotes: 1

Related Questions