How to select distinct when i have multiple rows?

Question

I have table with multiple rows in two columns.

# Column_A # Column B
# 1        # photo01
# 1        # photo02
# 1        # photo03
# 2        # video01
# 2        # video02
# 3        # music01
# 3        # music02
# 3        # music03

So when i'm using SELECT DISTINCT Column_A i have 3 records: 1, 2, 3. When i'm using SELECT DISTINCT Column_A, Column_B i have all records. I want not duplicated rows from Column_A with first row from Column_B, 1:1.

SELECT DISTINCT ID_product, (SELECT photos FROM my_table) 
FROM my_table
ORDER BY ID_product

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Expected result:

# Column_A # Column B
# 1        # photo01
# 2        # video01
# 3        # music01

How can I do it?

Gordon Linoff · Accepted Answer · 2019-09-03 12:58:05Z

3

Use aggregation:

select column_a, min(column_b)
from t
group by column_a;

If you want an arbitrary match, you can use window functions stead:

select column_a, column_b
from (select t.*,
             row_number() over (partition by column_a order by newid()) as seqnum
      from t
     ) t
where seqnum = 1;

answered Sep 3, 2019 at 12:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to select distinct when i have multiple rows?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related