-1

The otherwise identical rows here can sometimes differ by a numerical value of one column, let's call it Group:

Col1 Col2 ..... Group
A B 100
A B 200
A C 100
A D 200

For a particular value of group, how do I select only the rows, for which it is the minimal one?

In the above listing, for example, group 200 shall only return one row -- the fourth one -- because the second row is also a member of group 100, and thus shall not be listed under the 200...

Thom A
  • 88,727
  • 11
  • 45
  • 75
Mikhail T.
  • 3,043
  • 3
  • 29
  • 46
  • How are you determining a row is the "2nd" - 2nd or 4th ordered by what? – Stu May 16 '23 at 22:37
  • @Stu, the listing in my question contains only four rows: 1st, 2nd, 3rd, and 4th. That's the order I'm referring to. – Mikhail T. May 16 '23 at 22:39
  • How is the 4th row the "minimum" row for `group` `200`? What determines that the 2nd row presented visually has a "lower" rather than the 4th? – Thom A May 17 '23 at 00:22
  • @Thom-A, the 4th row is the _only_ row for group 200 in my example. The other row containing the same group has to be skipped, because it also belongs to group 100, and 100 is less than 200. – Mikhail T. May 17 '23 at 15:28
  • *"the 4th row is the only row for group 200"* no it's not, the second row (visually) also had the value `200` for `group`. – Thom A May 17 '23 at 23:34
  • @Thom-A, the second row must be skipped, because it has the same values in all other columns as the first row. And the first row has group 100. That's, what the whole question is about. I want only the rows, for which the value of group is the smallest among all, where the other columns match. Multiple answers -- and lptr's comment -- offer solutions. – Mikhail T. May 18 '23 at 00:34

2 Answers2

1

Try example

create table test(Col1 varchar(1),Col2 varchar(1),GroupN int);
insert into test values
 ('A','B',100)
,('A','B',200)
,('A','C',100)
,('A','D',200)
;
with minGroup as(
  select Col1,Col2,min(GroupN) as GroupN
  from test
  group by Col1,Col2
)
select a.Col1,a.Col2,a.GroupN
from test a 
inner join minGroup b on a.Col1=b.Col1 and a.Col2=b.Col2
where b.GroupN=200

For GroupN=200 result is

Col1 Col2 GroupN
A D 200

For GroupN=100 result is

Col1 Col2 GroupN
A B 100
A B 200
A C 100

A clearer option, however, works in SQL Server 2008

select * 
from (
  select Col1,Col2,GroupN
     ,min(GroupN)over(partition by Col1,Col2) as minGroupN
  from test
) minGroup
where minGroupN=100

In query plan weight of table scan=22%, sort=77%, all other=1%. Nested queries in this case do not add complexity and do not cause performance loss. If you have index on (Col1,Col2 ...) query performance is adequately. Query plan

ValNik
  • 1,075
  • 1
  • 2
  • 6
0

You can do it using LEFT JOIN as follows :

select t.*
from mytable t
left join (
  select Col1, Col2
  from mytable
  where Group_ <> 200
) as s on t.Col1 = s.Col1 and t.Col2 = s.Col2
where Group_ = 200 and s.Col1 is null

Result :

Col1    Col2    Group_
A       D       200

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • But there can be multiple values of `Group` for the same set of otherwise identical rows -- not just two, but three or more. So excluding the specific value may not be enough, right? – Mikhail T. May 16 '23 at 22:43
  • The query will exclude any row found in other groups. have a look on this dataset https://dbfiddle.uk/E-1_qG4f – SelVazi May 16 '23 at 22:58