SELECT DISTINCT在一列上，返回多列，ms访问查询 [英] SELECT DISTINCT on one column, with multiple columns returned, ms access query

问题描述

我有一个返回大约20列的查询，但我需要它只有一列不同。



我的数据看起来像那样

 id val1 val2 val3 
 1 33 mk 
 1 32 mk 
 2 34 jv 
 4 47 hl 

结果应为

 id val1 val2 val3 
 1 33 mk 
 2 34 jv 
 4 47 hl 

我有

选择Distinct id，val1，val2，val3

FROM table1



由doe组合无效......

解决方案

尝试：

  WITH  cte < span class =code-keyword> AS  
（ SELECT  *，ROW_NUMBER（） OVER （ PARTITION   BY  id  ORDER   BY  val1  DESC ） AS  rn 
  FROM  MyTable 
）
  SELECT  * 
  FROM  cte 
  WHERE  rn =  1  

试试这个：

  SELECT  t1。< Field_List> 
  FROM  TableName1  AS  t1  INNER   JOIN （
  SELECT  MAX（val1） AS  val1 
  FROM  TableName1 
） AS  t2  ON  t1.id = t2.id  AND  t1.val1 = t2.val1 

I have a query which returns about 20 columns , but i need it to be distinct only by one column.

my data looks something like that

id val1 val2 val3
1    33   m    k
1    32   m    k
2    34   j    v
4    47   h    l

the result should be

id val1 val2 val3
1    33   m    k
2    34   j    v
4    47   h    l

I have
Select Distinct id, val1, val2, val3
FROM table1

Group by doe not work either...

解决方案

Try:

WITH cte AS
(   SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
    FROM MyTable
)
SELECT *
FROM cte
WHERE rn = 1

Try this:

SELECT t1.<Field_List>
FROM TableName1 AS t1 INNER JOIN (
SELECT MAX(val1) AS val1
FROM TableName1 
) AS t2 ON t1.id = t2.id AND t1.val1 = t2.val1

这篇关于SELECT DISTINCT在一列上，返回多列，ms访问查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！