CODEWITHSUNDEEP
sqloracle-databaseselectduplicatesdistinct
Mike
Mike

Reputation: 155

Remove duplicates from first column in Select (not Distinct) in Oracle

I am new to Oracle.

I have a Select as part of a larger query which returns the below result. My problem is that for the first column I only need the first appearance of each value (they are sorted ascending). The result should look like what you get when you remove duplicates in Excel (a Distinct does not work here, I guess, as I still need the other rows separately).

Can someone tell me how I can achieve this ?

My query (shortened): 

Select
   p.ID
   , p.SUB_ID
   , p.ITEM_NAME AS ITEM
   , p.ITEM_PRICE AS PRICE
   /* ... */
FROM
   PRICE_LIST p
WHERE
   /* ... */
GROUP BY
   p.ID
   , p.SUB_ID
   , p.ITEM_NAME
   , p.ITEM_PRICE
   /* ... */
ORDER BY
   p.ID, p.SUB_ID

Current result:

enter image description here

Required result:

enter image description here

Upvotes: 4

Views: 1085

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

Normally this sort of formatting should be handled in your presentation layer. That being said, we might be able to approximate what you want as follows:

WITH cte AS (
    SELECT DISTINCT
        p.ID, p.SUB_ID, p.ITEM_NAME AS ITEM, p.ITEM_PRICE AS PRICE,
        ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY p.SUB_ID) rn
    FROM PRICE_LIST p
    WHERE
    /* ... */
)
SELECT
    CASE WHEN t.rn = 1 THEN TO_CHAR(t.ID) ELSE '' END AS ID,
    t.SUB_ID, t.ITEM, t.PRICE
FROM cte t
ORDER BY
    t.ID,
    t.SUB_ID

Gordon's edit:

We could just write this without a subquery/CTE at all, using ROW_NUMBER() directly in the CASE expression:

SELECT DISTINCT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY p.SUB_ID) = 1
         THEN TO_CHAR(p.ID) ELSE '' END AS ID,
    p.SUB_ID, p.ITEM_NAME AS ITEM, p.ITEM_PRICE AS PRICE
FROM PRICE_LIST p
WHERE
    /* ... */
ORDER BY
    p.ID,
    p.SUB_ID

Upvotes: 5

김성곤
김성곤

Reputation: 1

    SELECT 
      DECODE(RN, 1, ID, '') AS ID
    , SUB_ID
    , ITEM
    , PRICE
    , RN 
FROM (
    Select
       p.ID
       , p.SUB_ID
       , p.ITEM_NAME AS ITEM
       , p.ITEM_PRICE AS PRICE
       /* ... */
       , ROW_NUMBER() OVER(PARTITION BY P.ID ORDER BY P.SUB_ID) AS RN
    FROM
       PRICE_LIST p
    WHERE
       /* ... */
    GROUP BY
       p.ID
       , p.SUB_ID
       , p.ITEM_NAME
       , p.ITEM_PRICE
       /* ... */
    ORDER BY
       p.ID, p.SUB_ID
   )

Upvotes: 0

Related Questions