CODEWITHSUNDEEP
sqloracle-databaseselectduplicates
AJFaraday
AJFaraday

Reputation: 2450

Oracle SQL: Retrieving a record more than once

I'm using Oracle 11 and would like to be able to retrieve a record more than one in a query, which would be a good convenience saving for the next part of my code.

Let's consider this SQL statement:

SELECT ID, NAME FROM PEOPLE WHERE NAME IN ('Alice', 'Bob', 'Alice');

It returns this data:

| 1 | Alice | 
| 2 | Bob   |

What I'd really like to do is to un-uniquify that list and return the records with duplicates, in the order given. So the above statement would be:

| 1 | Alice | 
| 2 | Bob   |
| 1 | Alice |

I appreciate that Oracle is optimized to remove repetition like this, and I could re-use the data afterwards, keep it in a store object and retrieve by name etc. I was just wondering if there was a way to make this happen on the database itself.

Upvotes: 3

Views: 143

Answers (4)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Here's another idea:

WITH cteNumbers as (SELECT LEVEL AS N
                      FROM DUAL
                      CONNECT BY LEVEL <= 2),
     PEOPLE AS (SELECT 'Bob' AS NAME, 111 AS EMPID FROM DUAL UNION ALL
                SELECT 'Carol' AS NAME, 222 AS EMPID FROM DUAL UNION ALL
                SELECT 'Ted' AS NAME, 333 AS EMPID FROM DUAL UNION ALL
                SELECT 'Alice' AS NAME, 444 AS EMPID FROM DUAL)
SELECT *
  FROM PEOPLE p
  CROSS JOIN cteNumbers
  WHERE 1 = CASE
              WHEN NAME = 'Alice' THEN 1
              WHEN NAME = 'Bob' AND N = 1 THEN 1
              WHEN NAME = 'Ted' AND N < 4 THEN 1
              WHEN NAME = 'Carol' AND N = 3 THEN 1
              ELSE 0
            END
  ORDER BY NAME, N

Basically, use cteNumbers to generate a list of number (in this case, from 1 to 2 - adjust the CONNECT BY LEVEL condition to control how many numbers are generated), then use the CASE expression in the WHERE clause to control the circumstances under which a particular record's repetitions are selected.

SQLFiddle here

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

Late to the party but just wanted to add you can use a traditional table expression:

select p.id, p.name
  from (
    select 'Alice' as name from dual
    union all select 'Bob' from dual
    union all select 'Alice' from dual
  ) searched s
  join people p on p.name = s.name;

Upvotes: 1

Baskaran
Baskaran

Reputation: 64

query below for record with duplicate 

select x.id,x.name from (
  select a.id,a.name from people a where a.name in ('Alice')
  union all
  select a.id,a.name from people a where a.name in ('Bob')
  union all
  select a.id,a.name from people a where a.name in ('Alice')
) x

Upvotes: 3

Mureinik
Mureinik

Reputation: 311163

Oracle has a couple of handy built-in functions that return lists of arguments that you can then transform to a table and join on it. In your case, odcivarchar2list can be used to return a list of varchar2s:

SELECT p.*
FROM   TABLE(sys.odcivarchar2list('Alice', 'Bob', 'Alice')) dups
JOIN   people p ON p.name = dups.column_value*

Upvotes: 4

Related Questions