选择具有最高ID和特定列值的行

Question

我遇到了SQL查询问题。我有以下表格：

╔════╦═══════════════╦═════════════╦══════════╗ 
║ ID ║  NAME  ║ CITY  ║ PCNUMBER ║ 
╠════╬═══════════════╬═════════════╬══════════╣ 
║ 1 ║ John Doe  ║ New York ║  42 ║ 
║ 2 ║ John Doe  ║ New York ║  42 ║ 
║ 3 ║ John Doe  ║ New York ║  42 ║ 
║ 4 ║ John Doe  ║ Los Angeles ║  42 ║ 
║ 5 ║ Ellen Ripley ║ Nostromo ║  2092 ║ 
║ 6 ║ James T. Kirk ║ Riverside ║  2233 ║ 
║ 7 ║ Henry Jones ║ Chicago  ║  1899 ║ 
╚════╩═══════════════╩═════════════╩══════════╝ 

现在我想获得具有指定PCNUMBER行（可超过1），但它只能返回1分的结果为每一个独特PCNUMBER

所以，让我们假设我想从PCNUMBER 42和2092的数据结果将是：

╔════╦══════════════╦═════════════╦══════════╗ 
║ ID ║  NAME  ║ CITY  ║ PCNUMBER ║ 
╠════╬══════════════╬═════════════╬══════════╣ 
║ 4 ║ John Doe  ║ Los Angeles ║  42 ║ 
║ 5 ║ Ellen Ripley ║ Nostromo ║  2092 ║ 
╚════╩══════════════╩═════════════╩══════════╝ 

我试过很多东西，但我似乎无法产生这种:(希望你们能帮助我

对不起，如果有人已经有类似的问题，但我找不到一个。

Answer 1

SQL Server支持Common Table Expression和Windowing Functions。

WITH records 
AS 
(
    SELECT ID, Name, City, PCNUMBER, 
      ROW_NUMBER() OVER(PARTITION BY PCNUMBER ORDER BY ID DESC) rn 
    FROM TableName 
) 
SELECT ID, Name, City, PCNUMBER 
FROM records 
WHERE rn = 1 AND 
     PCNUMBER IN (42, 2092) 

• SQLFiddle Demo
• TSQL Ranking Functions

OUTPUT

╔════╦══════════════╦═════════════╦══════════╗ 
║ ID ║  NAME  ║ CITY  ║ PCNUMBER ║ 
╠════╬══════════════╬═════════════╬══════════╣ 
║ 4 ║ John Doe  ║ Los Angeles ║  42 ║ 
║ 5 ║ Ellen Ripley ║ Nostromo ║  2092 ║ 
╚════╩══════════════╩═════════════╩══════════╝ 

Answer 2

一种方式;

select T.* from T 
    inner join (
     select 
      max(id) id 
     from T 
     where PCNUMBER in (42, 2092) 
     group by PCNUMBER 
    ) mx on mx.id = T.id