选择按id排序的唯一行

I have two tables

我有两个表

Table A

表一个

+-----+---------+---------+
| pid | name    | country |
+-----+---------+---------+
|  1  | ABC     | XXXXXXX |
|  2  | EFG     | YYYYYYY |
|  3  | IJK     | ZZZZZZZ |
|  4  | LMN     | AAAAAAA |
|  5  | OPQ     | BBBBBBB |
|  6  | RST     | CCCCCCC |
+-----+---------+---------+

Table B

表B

+-----+---------+
| id  | pid     |
+-----+---------+
|  5  |   5     | 
|  4  |   1     | 
|  3  |   2     | 
|  2  |   5     | 
|  1  |   2     |
+-----+---------+

I want the below output

我想要下面的输出。

+-----+---------+---------+
| pid | name    | country |
+-----+---------+---------+
|  5  | OPQ     | BBBBBBB |
|  1  | ABC     | XXXXXXX |
|  2  | EFG     | YYYYYYY |
+-----+---------+---------+

That is the output should get the latest entries of Table B id field and the data of table A should be shown on appearing sequence of pid from Table B

这是输出应该得到表B id字段的最新条目，表A的数据应该显示在从表B中显示的pid序列上。

4 个解决方案

#1

You need to both join the tables together and to get the most recent value. Here is one method:

您需要将这些表连接在一起，并获得最新的值。这里有一个方法:

select a.pid, a.name, a.country
from a join
     b
     on a.pid = b.pid
where b.id = (select max(b2.id) from b b2 where b2.pid = b.pid);

#2

Try this:

试试这个:

SELECT DISTINCT A.PID, A.NAME, A.COUNTRY
FROM TABLE_A A
INNER JOIN TABLE_B B
ON A.PID = B.PID
ORDER BY A.PID

#3

You can also use row_number() :

您还可以使用row_number():

select top (1) with ties t1.*
from table1 t1 inner join
     table2 t2
     on t2.pid = t1.pid
order by row_number() over (partition by t2.pid order by t2.id desc);

#4

You dont need a join or fields from B?

你不需要B的加入或字段吗?

SELECT a.pi, a.name, a.country
FROM A AS a
WHERE EXISTS(SELECT b.id FROM B AS b WHERE b.pid = a.pid)

#1