CODEWITHSUNDEEP
sqloracle-databaseoracle11g
m taufik akbar lubis
m taufik akbar lubis

Reputation: 1

displays only 1 data

I tried to query data, but the data is more than 1. I want to display only 1 data, which is the first data.

Query :

SELECT DISTINCT a.country_id,
                a.street_address,
                a.city,
                a.phone_number,
                a.location_id,
                b.ket
  FROM LOCATIONS a
  LEFT OUTER JOIN (SELECT DISTINCT y.country_id,
                                   y.country_name || ' ' || z.REGION_NAME AS ket
                     FROM countries y, regions z
                    WHERE y.REGION_ID = z.REGION_ID) b
    ON a.country_id = b.country_id;

enter image description here

Upvotes: 0

Views: 98

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You can use an analytic function such as DENSE_RANK() including ordering by country_id (presuming the ordering column from the image) in order to bring the first row(including ties) such as

SELECT country_id, street_address, city, phone_number, location_id, ket
  FROM
  (
    SELECT l.country_id,
           l.street_address,
           l.city,
           l.phone_number,
           l.location_id,
           c.country_name || ' ' || r.region_name AS ket,
           DENSE_RANK() OVER (ORDER BY l.country_id) AS dr
      FROM locations l
      LEFT JOIN countries c
        ON l.country_id = c.country_id
      LEFT JOIN regions r
        ON c.region_id = r.region_id
    )
  WHERE dr = 1

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

If you just want to get first row from your query please try this.

 SELECT DISTINCT a.country_id, a.street_address, a.city, a.phone_number, a.location_id, b.ket FROM LOCATIONS a LEFT OUTER JOIN (SELECT DISTINCT y.country_id, y.country_name || ' ' || z.REGION_NAME AS ket FROM countries y, regions z WHERE y.REGION_ID=z.REGION_ID) b ON a.country_id = b.country_id
order by a.country_id
    fetch first 1 rows only;

(I have added order by clause (assumed that you want the rows with first country). Without order by clause it's not guaranteed to have same rows every time.)

Upvotes: 0

Related Questions