如何在表的一个字段中按最小值分组，保留同一行中的所有值？

I have a table like this:

我有这样一张桌子：

Field1 | Field2 | Field3
1      | 1      | 22
1      | 2      | 10
2      | 5      | 40
2      | 2      | 55

I want to group by Field1, and then take the values from the rest of the row with the minimal Field2, eg:

我想按Field1分组，然后使用最小的Field2从行的其余部分获取值，例如：

Field1 | Field2 | Field3
1      | 1      | 22
2      | 2      | 55

Note that this is not the same as selecting the minimum of each row, which would give:

请注意，这与选择每行的最小值不同，这将给出：

Field1 | Field2 | Field3
1      | 1      | 10
2      | 2      | 40

Which with my data would be a meaningless result.

哪些与我的数据将是一个毫无意义的结果。

Does anyone have a general (ie. multi database) solution to this? I'm sure it must be a solved problem!

有人有一个通用（即多数据库）解决方案吗？我敢肯定它一定是个问题！

I could really do with a solution that works in both sqlite and ms-access, and sql server would be a bonus.

我真的可以使用在sqlite和ms-access中工作的解决方案，而sql server将是一个奖励。

2 个解决方案

#1

You'll have to execute this with subqueries.

您必须使用子查询执行此操作。

Select * from 
 yourtable t
  inner join 
    (   Select field1, min(field2) as minField2 
        from yourtable 
        group by field1
     ) xx 
    on t.field1 = xx.field1 and t.field2 = xx.minfield2

Now, if you have multiple rows for a minimal field2 value, then you'll have dupes...If you don't want that (i.e. you want the minimal value of field3 for every minimal value of field2) in that case, you'd need another sub query:

现在，如果你有一个最小的field2值的多行，那么你将有dupes ...如果你不想要那个（即你想要field2的每个最小值的field3的最小值），那么你需要另一个子查询：

Select outert.field1, outert.field2, outert.field3
from yourtable outert 
inner join 

( 
 Select t.field1, xx.minfield2, min(field3) as minfield3 from 
 yourtable t
  inner join 
    (   Select field1, min(field2) as minField2 
        from yourtable 
        group by field1
     ) xx 
    on t.field1 = xx.field1 and t.field2 = xx.minfield2
 group by t.field1, xx.minfield2
) outerx
on outerx.field1 = outert.field1 
and outerx.field2 = outert.minfield2
and outerx.field3 = outert.minfield3

#2

As you can see there is a solution that works using only standard SQL, but it's long and complicated.

正如您所看到的，有一种解决方案仅使用标准SQL，但它很长且很复杂。

Note that it's also possible to write a "Hello, world!" program that works correctly in three different programming languages. Doing this doesn't usually add any value to your program though. It's much easier just to write the program three times, tailored to the specific syntax of each language.

请注意，也可以写一个“Hello，world！”程序在三种不同的编程语言中正常工作。这样做通常不会为您的程序增加任何价值。根据每种语言的特定语法量身定制三次编程程序要容易得多。

I think with SQL it is often better to forget trying to find a solution that works in all RDBMS and use the specific vendor extensions and idioms that make queries like this easier. For example in MS SQL Server, you could do something like this:

我认为使用SQL通常最好忘记尝试找到适用于所有RDBMS的解决方案，并使用特定的供应商扩展和习惯用法使这样的查询更容易。例如，在MS SQL Server中，您可以执行以下操作：

SELECT Field1, Field2, Field3
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field2) AS rn
    FROM table1
) AS T1
WHERE rn = 1

Anyway, you already have a general solution, so I just thought I'd offer this alternative viewpoint.

无论如何，你已经有了一个通用的解决方案，所以我只是想我会提供这个替代观点。

#1