How to INNER JOIN only one row from second table

Question

I have a issue joining only one row from the second table

statement:

 SELECT ART.*, EAN.*
   FROM ART,EAN 
  WHERE ART.ARTNR = ean.unit_artnr
    AND ean.typ = 'LE4';

TABLE EAN has sometimes 2 entries and so i get my

ART.ARTNR

doubled in the output.

i cant figure a way out to only include one row from EAN

i tried with group by but that does not do the trick because i need the * selects

sample data: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3f0a57795fdeb6745155a6e823643c40

now if i execute my statement it shows 2 times ART.ARTNR 1234556 and 2 times 23455 but this should not be the case each ARTNR should be shown once

Answer 1

select ART.*, EANC.*
from ART
  inner join (select EAN.EAN_ID,
                     EAN.EAN,
                     EAN.UNIT_ARTNR,
                     EAN.TYP,
                     row_number() over (partition by EAN.UNIT_ARTNR order by EAN.EAN_ID) as "ROWNR" 
              from EAN) EANC on ART.ARTNR = EANC.UNIT_ARTNR
where EANC.ROWNR = 1

The partition-by was right from @Zaynul's answer, but he used the wrong column to partition over.

In my example I order the rows of EAN by EAN.EAN_ID. If you want some other order, change it here.

The result in your example is (you can omit the ROWNR)

ARTNR   EAN_ID  EAN         UNIT_ARTNR  TYP     ROWNR
23455   5       7771234568  23455       LE4     1
1234556 1       1234        1234556     LE4     1