CODEWITHSUNDEEP
sqloracle-database
Chris Melville
Chris Melville

Reputation: 1518

Easier way to limit rows in SELECT subquery?

I perform queries on an Oracle database. Let's say I have a table, PEOPLE. Each person can have multiple reference numbers. The reference numbers are stored in a different table, REFERENCENUMBERS.

REFERENCENUMBERS contains a column, PERSON_ID, which is identical to the ID column of the PEOPLE table. It is through this ID that the tables are joined.

Let's say I want to perform a query on the PEOPLE table. However I only want a single reference number returned per person record: i.e if a person has multiple reference numbers, I don't want multiple rows returned per person per reference number.

I choose a criterion for how to select only one reference number: the one which was created earliest. The date of reference number creation is stored in the REFERENCENUMBERS table as DATECREATED.

The following code does this job:

SELECT
    PEOPLE.ID,
    PEOPLE.NAME,
    PEOPLE.AGE,
    PEOPLE.ADDRESS,
    -- Subquery to return the earliest-created reference number for this person
    (
    SELECT
        REFERENCENUMBERS.NUMBER 
    FROM
        REFERENCENUMBERS
    WHERE
        REFERENCENUMBERS.PERSON_ID = PEOPLE.ID -- Link back to the main people ID
        AND REFERENCENUMBERS.DATECREATED = 
        -- Sub-sub query simply to match the earliest date
        (
        SELECT
            MIN(R.DATECREATED) -- To ensure that only the earliest-created reference number is returned.
        FROM
            REFERENCENUMBERS R -- Give this sub-sub query an alias for the table
        WHERE
            R.PERSON_ID = PEOPLE.ID -- Link back to the main people ID
        )
    )
FROM
    PEOPLE
WHERE
    PEOPLE.AGE > 18 -- Or whatever

However, my question to you knowledgeable SQL people, is.. is there an easier way of doing this? It just appears cumbersome to have to include a sub-sub-query solely for the purpose of finding the earliest date, and limiting the WHERE clause of the sub-query.

There must be an easier, or cleaner way of doing this. Any suggestions?

(By the way - the sample code is greatly simplified from what I'm actually working on. Please don't provide answers which substantively modify my primary query with different-style JOINs etc - thanks).

Upvotes: 1

Views: 563

Answers (2)

William Robertson
William Robertson

Reputation: 15991

The simplest would be a top-n filter:

select people.id
     , people.name
     , people.age
     , people.address
     , ( select referencenumbers.number
         from   referencenumbers
         where  referencenumbers.person_id = people.id
         order by referencenumbers.datecreated
         fetch first row only )
from   people
where  people.age > 18;

More details here (requires Oracle 12.1 or later.)

Or this (works in earlier versions):

select people.id
     , people.name
     , people.age
     , people.address
     , ( select min(rn.person_id) keep (dense_rank first order by rn.datecreated)
         from   referencenumbers rn
         where  rn.person_id = people.id )
from   people
where  people.age > 18;

(I gave referencenumbers a shorter alias for readability.)

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

Try this

SELECT
    PEOPLE.ID,
    PEOPLE.NAME,
    PEOPLE.AGE,
    PEOPLE.ADDRESS,
    REFERENCENUMBERS.NUMBER 
FROM PEOPLE
JOIN REFERENCENUMBERS ON REFERENCENUMBERS.PERSON_ID = PEOPLE.ID -- Link back to the main people ID
JOIN
(
    SELECT
        R.PERSON_ID, 
        MIN(R.DATECREATED) minc -- To ensure that only the earliest-created reference number is returned.
    FROM
        REFERENCENUMBERS R -- Give this sub-sub query an alias for the table
    GROUP BY R.PERSON_ID
) t ON t.minc = REFERENCENUMBERS.DATECREATED and
       t.PERSON_ID = REFERENCENUMBERS.PERSON_ID
WHERE
    PEOPLE.AGE > 18 -- Or whatever

Upvotes: 1

Related Questions