Search code examples
sqlselectduplicatesfirebird

Select record field ignoring duplicate (indexed table)

I have an indexed table "Quotes" (PK, QUOTE, AUTHOR, URL, CATEGORY):

For example:

 PK    QUOTE               AUTHOR     URL                                             CATEGORY
 *********************************************************************************************
 1     SOME QUOTE TEXT     DARWIN     https://fr.wikipedia.org/wiki/Charles_Darwin    NATURE
 2     SOME OTHER QUOTE    NEWTON     https://fr.wikipedia.org/wiki/Isaac_Newton      SOCIETY
 3     ANOTHER QUOTE TEXT  MANDELA    https://fr.wikipedia.org/wiki/Nelson_Mandela    VARIOUS
 4     SOME QUOTE TEXT     DARWIN     https://fr.wikipedia.org/wiki/Charles_Darwin    RELIGION
 5     BLA BLA BLA BLA     NEWTON     https://fr.wikipedia.org/wiki/Isaac_Newton      SOCIETY

In a new query I want to select only fields (NAME and URL) but ignoring duplicates records.

The table already has a primary key index. The goal is to select authors from QUOTES table and to avoid creating a duplicate author for the user if this one already exists.

For my example above, my query should return only:

 AUTHOR     URL
 **********************************************************
 DARWIN     https://fr.wikipedia.org/wiki/Charles_Darwin
 NEWTON     https://fr.wikipedia.org/wiki/Isaac_Newton
 MANDELA    https://fr.wikipedia.org/wiki/Nelson_Mandela

Ignoring the two duplicates rows for (NEWTON and DARWIN).

Solution

  • The simplest solution is to use DISTINCT:

    select distinct AUTHOR, URL
from "Quotes"

    This will output each combination of AUTHOR and URL once (assuming they are not blob types).