How to get only first row grouped by multiple columns?-CodePudding

I want to select a few columns, but only keep the first row grouped by the columns.

If I use

select distinct item_no, item_type, item_name
from `table`
where item_name!='UNKNOWN'

It can give me two rows for one item_no, if item_type and item_name don't match in the two rows.

sample output:

item_no  item_name  item_type
1        table      A
1        tables     A
2        chair      B

How do I make sure I only get the first row grouped by three columns?

Expected output:

item_no  item_name  item_type
1        table      A
2        chair      B

I'm using BigQuery so standard SQL

CodePudding user response：

You can do a group by item_no then get the mininum value of item_name and item_type.

    SELECT 
            item_no,
            MIN(item_name) as item_name,
            MIN(item_type) as item_type
    FROM 
            `table` 
    GROUP BY
            item_no

CodePudding user response：

You can use Row_NUMBER() method over item_no and then in outer query you can select only those rows where row_number is 1 (i.e. its first occurrence, ascending ). e.g.

```SELECT item_no, item_name, item_type
   FROM (
       SELECT *
       ROW_NUMBER() OVER ( PARTITION BY item_no ) as row_number
       FROM <table_name>) SOURCE
   WHERE SOURCE.row_number = 1```