仅从SQL Server数据库表中选择具有另一列作为最大值的行

I'm trying to select rows from a Microsoft SQL Server Database table that for each ID in one column (ID) select only rows that have another column as the maximum (IDtoBeMaxed). For example:

我正在尝试从Microsoft SQL Server数据库表中选择行,对于一列(ID)中的每个ID,仅选择具有另一列作为最大值的行(IDtoBeMaxed)。例如:

From this table

从这张表

  ID  SomeValue IDtoBeMaxed
  1   56        2  
  1   59        2  
  1   80        1
  2   55        5
  2   80        5
  2   56        3
  2   81        3

I only want

我只想要

  ID  SomeValue IDtoBeMaxed
  1   56        2  
  1   59        2  
  2   55        5
  2   80        5

I have this query which works, but I think there should be a faster way that reduces the response time of the query. So again, this query gives me what I want, I am looking for a faster response time query.

我有这个查询有效,但我认为应该有一种更快的方法来减少查询的响应时间。再次,这个查询给了我想要的东西,我正在寻找一个更快的响应时间查询。

SELECT * 
FROM myTable 
WHERE ID = 2 
  AND IDtoBeMaxed = (SELECT MAX(IDtoBeMaxed) FROM myTable WHERE ID = 2)

Edits: I don't need all the rows, like I showed in the example above. I only need rows for a specific ID, just like the query that I wrote myself.

编辑:我不需要所有行,就像我在上面的例子中所示。我只需要特定ID的行,就像我自己编写的查询一样。

Results: I tried Shavkat query but it's not any faster than the query I had written. The rest of the answers return all the rows which I am not looking for. So this is still up for debate. But thanks for your answers so far.

结果:我尝试了Shavkat查询,但它并不比我写的查询快。其余的答案返回我不想要的所有行。所以这还有待讨论。但是感谢你到目前为止的答案。

4 个解决方案

#1

Are you trying to return all rows from the table in one query? Something like the following would work:

您是否尝试在一个查询中返回表中的所有行?像下面这样的东西会起作用:

SELECT t.Id,t.SomeValue,t.IDtoBeMaxed
FROM MyTable t
JOIN (
    SELECT Id,MAX(IDToBeMaxed) AS MaxId
    FROM MyTable
    GROUP BY Id
) AS m ON t.Id=m.Id AND t.IDToBeMaxed=m.MaxId

In response to your edits:

回复您的修改:

The query you've written is about as simple as you can get. If you need to improve response time you should look at creating an index on the ID and IDToBeMaxed columns.

你写的查询就像你能得到的一样简单。如果需要缩短响应时间,应该考虑在ID和IDToBeMaxed列上创建索引。

CREATE INDEX ix_IDMaxed ON MyTable(ID,IDToBeMaxed)

Is that an option for you?

这是你的选择吗?

#2

Here is an old-school one:

这是一个老派的:

declare @myTable table (ID int,  SomeValue int, IDtoBeMaxed int)
insert into @myTable
values
  (1,   56,        2 ), 
  (1,   59,        2 ),  
  (1,   80,        1 ),
  (2,   55,        5 ),
  (2,   80,        5 ),
  (2,   56,        3 ),
  (2,   81,        3 ),
  (3,   55,        5 ),
  (3,   80,        5 ),
  (3,   56,        5 ),
  (3,   81,        5 )

select a.*
from @myTable a left join @myTable b 
on a.ID = b.ID and a.IDtoBeMaxed < b.IDtoBeMaxed
where b.ID is null and a.ID = 2

#3

Could you please try below...

你可以试试下面......

WITH    selectOne
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY SomeValue, ID ORDER BY IDtoBeMaxed DESC ) AS RowNum ,
                        ID ,
                        SomeValue ,
                        IDtoBeMaxed
               FROM     myTable
             )
    SELECT  *
    FROM    selectOne
    WHERE   RowNum = 1

#4

The following example return the records ranked by their IDtoBeMaxed

以下示例返回按IDtoBeMaxed排名的记录

;WITH cte AS
 (SELECT *, DENSE_RANK() OVER(PARTITION BY ID ORDER BY IDtoBeMaxed DESC) AS dr
  FROM myTable
  )
  SELECT *
  FROM cte
  WHERE dr = 1

Demo on SQLFiddle

在SQLFiddle上演示

#1