blocks|key|1399232|text|SELECT+*+FROM
GPSReport+AS+G1
JOIN+(SELECT+device_serial,+max(datetime)+as+mostrecent+
++++++FROM+GPSReport+group+by+device_serial)+AS+G2
ON+G2.device_serial+=+G1.device_serial+and+g2.mostrecent+=+g1.datetime
ORDER+BY+G1.device_serial|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1399233|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT * FROM
GPSReport AS G1
JOIN (SELECT device_serial, max(datetime) as mostrecent 
 FROM GPSReport group by device_serial) AS G2
ON G2.device_serial = G1.device_serial and g2.mostrecent = g1.datetime
ORDER BY G1.device_serial
</code></pre>

blocks|key|1399380|text|WITH+DEDUPE+AS+(
++++SELECT++*
++++++++++,+ROW_NUMBER()+OVER+(+PARTITION+BY+what_you_want_for_distinct+ORDER+BY+what_you_want_for_distinct)+AS+OCCURENCE
++++FROM+tablename
++++)
SELECT++*+FROM+DEDUPE
WHERE
OCCURENCE+=+1+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1399381|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>WITH DEDUPE AS (
 SELECT *
 , ROW_NUMBER() OVER ( PARTITION BY what_you_want_for_distinct ORDER BY what_you_want_for_distinct) AS OCCURENCE
 FROM tablename
 )
SELECT * FROM DEDUPE
WHERE
OCCURENCE = 1 
</code></pre>

blocks|key|1399205|text|你有一个正确的连接，所以如果你在表GPSReport中有超过1个设备序列号的记录，它将获得所有这些记录和连接，然后到从SELECT+DISTINCT+device_serial+FROM+GPSReport接收的唯一列表。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1399206|entityMap^0|H|9|1N|18|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

You are having a right join, so if you have more than 1 record for device serial number in table <code>GPSReport</code>, it will get all those record and joint then to the unique list received from <code>SELECT DISTINCT device_serial FROM GPSReport</code>.

blocks|key|1399253|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1399254|+++Select+r.*+++
+++From+GPSReport+r
+++Where+datetime+=
++++++++(Select+Max(DateTime)
+++++++++From+GPSReport+
+++++++++Where+device_serial+=+r.device_serial)|code-block|syntax|javascript|1399255|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try:

<pre><code> Select r.* 
 From GPSReport r
 Where datetime =
 (Select Max(DateTime)
 From GPSReport 
 Where device_serial = r.device_serial)
</code></pre>

blocks|key|1399315|text|这样如何-因为我不能运行它，我想我的synatx不是很完美|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1399316|select+*
++from+(
++++select+device_serial,+[datetime],+triggerID,+latitude,+longitude,+speed,+[address],
++++++++ROW_NUMBER()+over+(partition+by+device_serial+order+by+device_serial+asc,+[datetime]+desc)+as+row
++++++from+gpsreport
++)+as+data
++where+row+=+1|code-block|syntax|javascript|1399317|如果存在具有相同device_serial和datetime的多个记录，则可能需要修改order+by子句以选择首选记录|1399318|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

How about something like this - since I couldn't run it, I expect my synatx is not perfect

<pre><code>select *
 from (
 select device_serial, [datetime], triggerID, latitude, longitude, speed, [address],
 ROW_NUMBER() over (partition by device_serial order by device_serial asc, [datetime] desc) as row
 from gpsreport
 ) as data
 where row = 1
</code></pre>

You may need to modify the order by clause to select the preferred record if there are multiples with the same device_serial and datetime

blocks|key|1183864|text|我会使用公用表表达式(CTE)，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1183865|With+ResultTable+(RowNumber
+++++++++++++++++,device_serial
+++++++++++++++++,datetime
+++++++++++++++++,triggerID
+++++++++++++++++,latitude
+++++++++++++++++,longitude
+++++++++++++++++,speed
+++++++++++++++++,address)
AS
(
++++SELECT+Row_Number()+OVER+(PARTITION+BY+device_serial
++++++++++++++++++++++++++++++++++ORDER+BY+datetime+DESC)
++++++++++,device_serial
++++++++++,datetime
++++++++++,triggerID
++++++++++,latitude
++++++++++,longitude
++++++++++,speed
++++++++++,address
++++++FROM+GPSReport
)
++++SELECT+device_serial
++++++++++,datetime
++++++++++,triggerID
++++++++++,latitude
++++++++++,longitude
++++++++++,speed
++++++++++,address
++++++FROM+ResultTable
+++++WHERE+RowNumber+=+1|code-block|syntax|javascript|1183866|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I would do it with a Common Table Expression (CTE), like so:

<pre><code>With ResultTable (RowNumber
 ,device_serial
 ,datetime
 ,triggerID
 ,latitude
 ,longitude
 ,speed
 ,address)
AS
(
 SELECT Row_Number() OVER (PARTITION BY device_serial
 ORDER BY datetime DESC)
 ,device_serial
 ,datetime
 ,triggerID
 ,latitude
 ,longitude
 ,speed
 ,address
 FROM GPSReport
)
 SELECT device_serial
 ,datetime
 ,triggerID
 ,latitude
 ,longitude
 ,speed
 ,address
 FROM ResultTable
 WHERE RowNumber = 1
</code></pre>

blocks|key|1399458|text|我在StackOverFlow上尝试了所有可能的答案后，发现了这个令人惊讶的结果|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1399459|WITH+cte+AS+/*+Declaring+a+new+table+named+'cte'+to+be+a+clone+of+your+table+*/
(SELECT+*,+ROW_NUMBER()+OVER+(PARTITION+BY+id+ORDER+BY+val1+DESC)+AS+rn
++++FROM+MyTable+/*+Selecting+only+unique+values+based+on+the+"id"+field+*/
)
SELECT+*+/*+Here+you+can+specify+several+columns+to+retrieve+*/
FROM+cte
WHERE+rn+=+1|code-block|syntax|javascript|1399460|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I found this amazing result after trying every possible answer on StackOverFlow

<pre><code>WITH cte AS /* Declaring a new table named 'cte' to be a clone of your table */
(SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
 FROM MyTable /* Selecting only unique values based on the "id" field */
)
SELECT * /* Here you can specify several columns to retrieve */
FROM cte
WHERE rn = 1
</code></pre>

blocks|key|1184008|text|以下是针对Postgresql+9%2B的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1184009|这些答案对我来说都不起作用(然而这是谷歌为我的搜索返回的第一个链接)。我只需要获取每组行中的第一行，其中给定表达式的计算结果为equal，同时删除其他行，而不使用任何聚合。|1184010|This+answer向我展示了如何使用DISTINCT+ON+(这与DISTINCT有所不同)：|offset|length|style|CODE|1184011|SELECT+DISTINCT+ON(x,y)+z,+k,+r,+t,+v
FROM+foo;|code-block|syntax|javascript|1184012|在这种情况下，只获取第一个z。则从集合中丢弃其余的z。|1184013|您可以只选择一列(这就是我所做的)，而不是像示例中那样选择两列。|1184014|请记住，由于没有GROUP+BY，因此不能在该查询中使用真正的聚合。|1184015|请查看链接中的答案以了解更多选项。它是非常彻底的。|1184016|entityMap|0|LINK|mutability|MUTABLE|url|https://dba.stackexchange.com/a/159899/137608^0|0|0|K|B|Z|8|0|B|0|0|0|D|1|P|1|0|0|8|8|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@$F|17|G|18|H|I]|$F|19|G|1A|H|I]]|9|@$F|1B|G|1C|1|1D]]|A|$]]|$1|J|3|K|5|L|7|1E|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1F|8|@$F|1G|G|1H|H|I]|$F|1I|G|1J|H|I]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1K|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1L|8|@$F|1M|G|1N|H|I]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1O|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

The following is for Postgresql 9+.

None of these answers worked for me (yet this was the first link returned by Google for my search). I needed to get only the first row of each set of rows where the given expressions evaluate to equal while dropping the other rows without using any aggregation.

<a href="https://dba.stackexchange.com/a/159899/137608">This answer</a> showed me how to do it with <code>DISTINCT ON</code> (which is different than just <code>DISTINCT</code>):

<pre><code>SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
</code></pre>

In that case, only the first <code>z</code> is taken. The rest of the <code>z</code>s are discarded from the set.

You can select just one column (which is what I did), instead of two like in the example.

Bear in mind that since there is no <code>GROUP BY</code>, you cannot use real aggregation in that query.

Check out the answer from the link for more options. It is very thorough.

I'm trying to write a query that returns the most recent GPS positions from a GPSReport table for each unique device. There are 50 devices in the table, so I only want 50 rows returned.

Here is what I have so far (not working)

<pre><code>SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1
RIGHT JOIN
(SELECT DISTINCT device_serial FROM GPSReport) AS G2
ON G2.device_serial = G1.device_serial
ORDER BY G2.device_serial, G1.datetime DESC
</code></pre>

This returns 50 rows, but is not returning a unique row for each device_serial. It returns all of the reports for the first device, then all of the reports for the second device, etc.

Is what I'm trying to do possible in one query?

SELECT DISTINCT on one column, return multiple other columns (SQL Server)

我正在尝试编写一个查询，为每个唯一的设备从GPSReport表中返回最新的GPS位置。表中有50个设备，所以我只想返回50行。这是我到目前为止所拥有的(不工作)SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1RIGHT JOIN(SELECT DISTINCT device_

问在一列上选择DISTINCT，返回多个其他列(SQL Server)
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在一列上选择DISTINCT，返回多个其他列(SQL Server)EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在一列上选择DISTINCT，返回多个其他列(SQL Server)
EN