How to get rows with minimum ID on a multiple columns query-CodePudding

I have a table like this:

Id	Type	multiple columns (a lot)...
1	50
2	50
3	50
4	75
5	75
6	75

I need to get only the rows with the older (min) id as a part of my query. The result should include all the columns of the table, but given that these multiple columns have multiple values, it's not posible to use MIN() and then GROUP BY

I need something like this:

Id	Type	multiple columns (a lot)...
1	50
4	75

I've tried using MIN() function and grouping by but that's not an option cause the rest of the columns have different values and if I use a GROUP BY I'm getting all the rows and not only the ones with the lowest ID's.

Any ideas?

Thanks!

CodePudding user response：

You can add a column that represents the number of dups. That result will be used to join only with unique rows. You can use Common table Expression to split the steps

WITH rows_with_index AS (
SELECT
    ROW_NUMBER() OVER(PARTITION BY <TYPE>) AS row_number,
    id,
    <TYPE>
FROM
    <TABLE>
) 
SELECT * FROM <TABLE> t 
 JOIN rows_with_index ON rows_with_index.id = t.id 
 AND rows_with_index.row_number = 1;

CodePudding user response：

You can use the WITH TIES option in concert with the window function lag() over()

To be clear, this will flag when the value changes

Example

Select top 1 with ties *
 From YourTable
 Order by case when lag([type],1) over (order by id) = [Type] then 0 else 1 end desc

Results

Id  Type
1   50
4   75

Based on Rodrigo's solution, you may have wanted the first [Type] regardless of sequence.

Select top 1 with ties *
 From  YourTable
 Order by row_number() over (partition by [Type] order by ID)