使用distinct和max的SQL查询

Question

我有一个像这样的数据集：

 type    seqID    text     
 A       1        Text1a
 A       2        Text2a
 A       3        Text3a
 B       1        Text1b
 B       2        Text2b

如何按类型返回具有按类型分组的最高 seqID 的行？因此，在上面的示例中，我希望返回包含 A, 3, Text3a 和 B, 2, Text2b 的行。

Answer 1

SELECT *  
FROM tmp t1  
WHERE NOT EXISTS  
(SELECT 1 FROM tmp t2 WHERE t1.type = t2.type AND t2.seqID > t1.seqID)  

它不应该存在具有相同类型和更高 seqID 的任何其他行。

Answer 2

您需要一个 ID，但由于“文本”对于此示例来说似乎是唯一的

CREATE TABLE #TMP 
 (type VARCHAR(3),   seqID  INT,   [text] varchar(256))
insert #TMP values ('A'   ,    1   ,     'Text1a')
insert #TMP values ('A'   ,    2   ,     'Text2a')
 insert #TMP values ('A'   ,    3   ,     'Text3a')
 insert #TMP values ('B'   ,    1   ,     'Text1b')
 insert #TMP values ('B'   ,    2   ,     'Text2b')


SELECT * FROM #TMP T
where [text] IN
    (SELECT TOP 1 [text] FROM #TMP t2 WHERE t.type = t2.type ORDER BY t2.seqID DESC)

Answer 3

SELECT tbl.*
FROM
   ( SELECT type, MAX(seqID)
     FROM tbl
     GROUP BY type) maxes
WHERE
   tbl.type= maxes.type AND
   tbl.seqID= maxes.seqID

Answer 4

SELECT t.* FROM
(
   SELECT type, MAX(seqID) as maxId 
   FROM Table
   GROUP BY type
) m
INNER JOIN Table t ON m.maxId = t.seqId 

使用热膨胀系数

;WITH maxIds(maxId)
AS
(
   SELECT type, MAX(seqID) as maxId 
   FROM Table
   GROUP BY type
)
SELECT t.* FROM
Table t
INNER JOIN maxIds m ON m.maxId = t.seqID

Answer 5

如果您使用的是 SQL Server 2005+，则可以使用 排名函数（更具体地说，ROW_NUMBER())：

SELECT
  type,
  seqID,
  text
FROM (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY type ORDER BY seqID DESC)
  FROM atable
) s
WHERE rnk = 1

Answer 6

create table #tlb1(
[type] VARCHAR(3), seqID  INT, [text] varchar(max)
)

declare @type varchar(3), @text varchar(max);
declare @seqID int;
declare seq_cursor cursor for 
select [type], max(seqID) from tbl group by [type]
open seq_cursor
fetch next from seq_cursor into @type,@seqID
while(@@fetch_status=0)
begin
    set @text= (select [text] from tbl where [type]=@type and seqID=@seqid);
    insert into #tlb1 values (@type, @seqID,@text);
    fetch next from seq_cursor into @type,@seqID    
end
select * from #tlb1
close seq_cursor
deallocate seq_cursor
truncate table #tlb1

Answer 7

尝试：

SELECT type, max(seqID),text 
FROM 'db'
GROUP BY type

就这么简单。

Answer 8

编辑解决方案。将此视为伪代码（因为我不熟悉 SQL Server 语法）：

SELECT a.type, a.seqID, a.text FROM table a
 JOIN
(SELECT type, max(seqID) seqID FROM table GROUP BY type) b 
ON a.seqID = b.seqID AND a.type=b.type