CODEWITHSUNDEEP
sqldatabaseoracle-database
akinKaplanoglu
akinKaplanoglu

Reputation: 768

Get row by row data according to some condition in oracle

I have a table in Oracle database. I want to getting data that has the same column value row by row. For example :

1   871412  007607000176    12.02.2015 15:03:29 007607000176    ELTN    007607000176    ELTN    52079   28  156608  654108
2   753281  007607000176    23.01.2014 13:13:38 007607000176    ELTN    007607000176    ELTN    43494   24  82860   580360
3   739033  007607000176    23.01.2014 13:10:53 007607000176    ELTN    007607000176    ELTN    43494   24  82860   580360
4   528902  007607000176    22.02.2013 16:19:37 007607000176    ELTN    007607000176    ELTN    46776   26  19521   517021
5   468832  007607000176    15.02.2012 16:00:58 007607000176    ELTN    007607000176    ELTN    44708   32  10395   442895
6   36782   007607000176    01.04.2011  007607000176    ELTN    007607000176    ELTN                
7   36781   007607000176    01.04.2010  007607000176    ELTN    007607000176    ELTN                
8   36780   007607000176    01.04.2009  007607000176    ELTN    007607000176    ELTN                
9   36779   007607000176    30.12.2007  007607000176    ELTN    007607000176    PRTG                
10  36778   007607000176    01.04.2007  007607000176    PRTG    007607000176    BASC                
11  36777   007607000176    01.04.2006  007607000176    BASC    007607000176    PRTG                
12  36776   007607000176    01.04.2005  007607000176    PRTG    007607000176    PRTG

I want the row 6-7-8 row by row like below.Because their some columns are sames.

ELTN    007607000176    ELTN                
ELTN    007607000176    ELTN                
ELTN    007607000176    ELTN

I want all the records in table like this situation and take row by row. How can I do ?

EDIT

I use WITH clause for solving this. First I brought the data with group. After that I brought the duplicate data information row by row.

Upvotes: 0

Views: 57

Answers (2)

MT0
MT0

Reputation: 167774

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST ( A, B, C, D, E, F, G, H, I, J, K, L ) AS
          SELECT 1,   871412,  '007607000176',    TIMESTAMP '2015-02-12 15:03:29', '007607000176',    'ELTN',    '007607000176',    'ELTN',    52079,   28,  156608,  654108 FROM DUAL
UNION ALL SELECT 2,   753281,  '007607000176',    TIMESTAMP '2014-01-23 13:13:38', '007607000176',    'ELTN',    '007607000176',    'ELTN',    43494,   24,  82860,   580360 FROM DUAL
UNION ALL SELECT 3,   739033,  '007607000176',    TIMESTAMP '2014-01-23 13:10:53', '007607000176',    'ELTN',    '007607000176',    'ELTN',    43494,   24,  82860,   580360 FROM DUAL
UNION ALL SELECT 4,   528902,  '007607000176',    TIMESTAMP '2013-02-22 16:19:37', '007607000176',    'ELTN',    '007607000176',    'ELTN',    46776,   26,  19521,   517021 FROM DUAL
UNION ALL SELECT 5,   468832,  '007607000176',    TIMESTAMP '2012-02-15 16:00:58', '007607000176',    'ELTN',    '007607000176',    'ELTN',    44708,   32,  10395,   442895 FROM DUAL
UNION ALL SELECT 6,   36782,   '007607000176',    TIMESTAMP '2011-04-01 00:00:00', '007607000176',    'ELTN',    '007607000176',    'ELTN', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 7,   36781,   '007607000176',    TIMESTAMP '2010-04-01 00:00:00', '007607000176',    'ELTN',    '007607000176',    'ELTN', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 8,   36780,   '007607000176',    TIMESTAMP '2009-04-01 00:00:00', '007607000176',    'ELTN',    '007607000176',    'ELTN', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 9,   36779,   '007607000176',    TIMESTAMP '2007-12-30 00:00:00', '007607000176',    'ELTN',    '007607000176',    'PRTG', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 10,  36778,   '007607000176',    TIMESTAMP '2007-04-01 00:00:00', '007607000176',    'PRTG',    '007607000176',    'BASC', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 11,  36777,   '007607000176',    TIMESTAMP '2006-04-01 00:00:00', '007607000176',    'BASC',    '007607000176',    'PRTG', NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 12,  36776,   '007607000176',    TIMESTAMP '2005-04-01 00:00:00', '007607000176',    'PRTG',    '007607000176',    'PRTG', NULL, NULL, NULL, NULL FROM DUAL

Query 1:

SELECT A, C, E, F, G, H, I, J, K, L
FROM   TEST t
WHERE EXISTS (
  SELECT 'X'
  FROM   TEST x
  WHERE  x.ROWID <> t.ROWID
  AND    x.C = t.C
  AND    x.E = t.E
  AND    x.F = t.F
  AND    x.G = t.G
  AND    x.H = t.H
  AND    (x.I = t.I OR ( x.I IS NULL AND t.I IS NULL ))
  AND    (x.J = t.J OR ( x.J IS NULL AND t.J IS NULL ))
  AND    (x.K = t.K OR ( x.K IS NULL AND t.K IS NULL ))
  AND    (x.L = t.L OR ( x.L IS NULL AND t.L IS NULL ))
)

Results:

| A |            C |            E |    F |            G |    H |      I |      J |      K |      L |
|---|--------------|--------------|------|--------------|------|--------|--------|--------|--------|
| 2 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN |  43494 |     24 |  82860 | 580360 |
| 3 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN |  43494 |     24 |  82860 | 580360 |
| 6 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN | (null) | (null) | (null) | (null) |
| 7 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN | (null) | (null) | (null) | (null) |
| 8 | 007607000176 | 007607000176 | ELTN | 007607000176 | ELTN | (null) | (null) | (null) | (null) |

Upvotes: 1

kl78
kl78

Reputation: 1666

One solution could be to join the table with itself, and check that every relevant column is the same, except the id. So you get only a join if you have a row with exactly same data except the id: 

   Select a.colname, a.colname, a.colname from table as a
   inner join table as b on a.Id != b.ID 
  and a.date = b.date and a.Code = b.Code ... all other relevant columns... and a.code2 = b.code2

Upvotes: 0

Related Questions