skip to Main Content

I have the following table (stripped down for demonstration)

products
- id
- part_number
- group_id

I want to be able to query against products and only return a single row per group_id (whichever is noticed first in the query is fine). All rows with group_id = null return as well.

Example:

ID      part_number    group_id
2314    ABB19          1
4543    GFH54          1
3454    GHT56          2
3657    QWT56          2
7689    GIT56          2
3465    HG567          null
5675    FG345          null

I would want to query against this table and get the following results:

ID      part_number    group_id
2314    ABB19          1
3454    GHT56          2
3465    HG567          null
5675    FG345          null

I have tried using group by but wasnt able to get it working without selecting the group_id and doing a group by on it which just returned a list of unique group_id’s. Given the complexity of my real products table its important that I am able to keep using select * and not naming each column I need to return.

2

Answers


  1. Chosen as BEST ANSWER

    I was able to solve this with a combination of DISTINCT ON and a UNION

    SELECT DISTINCT ON (group_id) * from products 
    WHERE group_id IS NOT NULL
    UNION 
    SELECT * FROM products 
    WHERE group_id IS NULL
    

  2. row_number() and filtering might be more efficient than distinct on and union all, which incur two table scans.

    select *
    from (
        select p.*,
            row_number() over(partition by group_id order by id) rn
        from products p
    ) p
    where rn = 1 or group_id is null
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search