CODEWITHSUNDEEP
sqlsql-servert-sql
Daniel Chen Yin
Daniel Chen Yin

Reputation: 37

Use Min variable without group by, while grouping by id

Suppose I have this table:

id ¦ value ¦ idRelation
------------------------
1  ¦ 1     ¦ 1234
1  ¦ 2     ¦ 1345
1  ¦ 3     ¦ 1456
2  ¦ 1     ¦ 8524
2  ¦ 2     ¦ 6547
3  ¦ 1     ¦ 9852
4  ¦ 2     ¦ 4569
4  ¦ 3     ¦ 4985

I need to use a min on the value variable grouping by id so that I can obtain the relationship id.

Basically I need the following result

id ¦ value ¦ idRelation
------------------------
1  ¦ 1     ¦ 1234
2  ¦ 1     ¦ 8524
3  ¦ 1     ¦ 9852
4  ¦ 2     ¦ 4569

I tried so far using WITH TIES but the problem is that I need the min of each id group not just the 1s.

Thanks in advance

Upvotes: 1

Views: 151

Answers (2)

Paweł Dyl
Paweł Dyl

Reputation: 9143

This should do the job:

SELECT id, value,idRelation FROM
  (SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY value) N FROM table) T
WHERE N=1

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Other option is to use row_number() function with ties clause:

select top (1) with ties t.*
from table t
order by row_number() over (partition by id order by value);

Upvotes: 2

Related Questions