SQL select one to many remove duplicates

Question

I have a table "A" against B one-to-many (A can have many Bs). I want to select all data from A and sort by fields from B, but:

select * from A a left outer join B b to b.a_id = a.id order by b.id;

It returns duplicates based on how many A has related B items. For example, when A is related to 3 x B then I have 3 duplicate rows from the selection.

When I try to use distinct and order by, I get a "not expression SELECTed" exception.

I tried adding "fetch first 10 rows only" and it only works when I have more than 10 results.

Can it be done at all? I want to test it in sql first and then write it in java. Please help!

I am expecting list of A's ordered by fields from B without duplicates..

B have different fields than A, suppose A has ID, name, phone number and B has ID, description, tag. I want to order A for example by B.description (depends on what user select)

Example: img

When im ordering by B.description, if 3 B's have not null description, then i have 3 duplicated rows of A. If there is only 2 B's with not null description, then i have 2 duplicated rows

User table:

    id     name           age
---------- -------------- -------------
         1 Robert         22
         2 Anna           14
         3 Patrick        15
         4 Ola            86


Contact table:

    id     email          phone         user_id
---------- -------------- ------------- -----------
        1  example@gmail  12312321      1
        1  dr@gmail       333331        1
        1  ajax@gmail     9971121       1
        2  ACCOUNTING     33434343      2
        2  test@test.pl   33434343      2
        3  wrongemal@w.pl 11111111      3
        4  x@x.pl         55555555      4

    SQL> select distinct user.id, user.name, user.age
      2  from User user left outer join UserContact contact on contact.user_id = user.id
      3  order by details.email;

It returns: 

    id     name           age
---------- -------------- -------------
         2 Anna           14
         2 Anna           14
         1 Robert         22
         1 Robert         22
         1 Robert         22
         3 Patrick        15
         4 Ola            86

I want to sort by email so i expect:

    id     name           age
---------- -------------- -------------
         2 Anna           14
         1 Robert         22
         3 Patrick        15
         4 Ola            86

Yes, it can be done. Do you want only one related B for each A? If there are three Bs, which one do you want? The oldest one, the newer one, any? — The Impaler, Mar 02 '23 at 21:05
Please add a few rows of sample data and the expected result. — The Impaler, Mar 02 '23 at 22:02
First, filter the user contacts so that you are returning one row per user and then join and order the result set. — MT0, Mar 03 '23 at 00:44

score 0 · Answer 1 · answered Mar 02 '23 at 21:10

I don't have your tables so I'll illustrate it using Scott's sample schema.

This works, but returns duplicate rows:

SQL> select a.*
  2  from dept a left outer join emp b on b.deptno = a.deptno
  3  order by b.deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

15 rows selected.

If you apply DISTINCT to it, Oracle raises an error (ORA-01791: not a SELECTed expression). See the asterisk pointing to error location? It is the b. table alias. What's wrong with that query? It selects distinct a.* but sorts data by b. column, which is not in SELECT column list as it contains only columns from the a. table:

SQL> select distinct a.*
  2  from dept a left outer join emp b on b.deptno = a.deptno
  3  order by b.deptno;
order by b.deptno
         *
ERROR at line 3:
ORA-01791: not a SELECTed expression

OK then, switch to order by a.deptno, and it works (and returns distinct data set):

SQL> select distinct a.*
  2  from dept a left outer join emp b on b.deptno = a.deptno
  3  order by a.deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

But B may have different fields than A, suppose A has ID, name, phone number and B has ID, description, tag. I want to order A by B.description — Patryk, Mar 02 '23 at 21:17
When im ordering by B.description, if 3 B's have not null description, then i have 3 duplicated rows of A. If there is only 2 B's with not null description, then i have 2 duplicated rows — Patryk, Mar 02 '23 at 21:46

SQL select one to many remove duplicates

1 Answers1