blocks|key|2913048|text|如果date和col_date是同一列，则只需执行以下操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2913049|SELECT+A,+MAX(date)+FROM+t+GROUP+BY+A|code-block|syntax|javascript|2913050|为什么不使用：|2913051|WITH+x+AS+(+SELECT+A,+MAX(col_date)+m+FROM+TABLENAME+GROUP+BY+A+)
SELECT+t.A,+t.date+FROM+TABLENAME+t+JOIN+x+ON+x.A+=+t.A+AND+x.m+=+t.col_date|2913052|否则：|2913053|SELECT+A,+FIRST_VALUE(date)+KEEP(dense_rank+FIRST+ORDER+BY+col_date+DESC)
++FROM+TABLENAME
+GROUP+BY+A|2913054|entityMap^0|2|4|7|8|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|12|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|T|$]]

If <code>date</code> and <code>col_date</code> are the same columns you should simply do:
<pre><code>SELECT A, MAX(date) FROM t GROUP BY A
</code></pre>
Why not use:
<pre><code>WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME GROUP BY A )
SELECT t.A, t.date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
</code></pre>
Otherwise:
<pre><code>SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)
 FROM TABLENAME
 GROUP BY A
</code></pre>

blocks|key|244626|text|解析函数方法看起来像这样|type|unstyled|depth|inlineStyleRanges|entityRanges|data|244627|SELECT+a,+some_date_column
++FROM+(SELECT+a,
+++++++++++++++some_date_column,
+++++++++++++++rank()+over+(partition+by+a+order+by+some_date_column+desc)+rnk
++++++++++FROM+tablename)
+WHERE+rnk+=+1|code-block|syntax|javascript|244628|请注意，根据您希望如何处理关联(或者在数据模型中是否可以关联)，您可能希望使用ROW_NUMBER或DENSE_RANK分析函数而不是RANK。|offset|length|style|CODE|244629|entityMap^0|0|0|13|A|1E|A|1V|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

The analytic function approach would look something like

<pre><code>SELECT a, some_date_column
 FROM (SELECT a,
 some_date_column,
 rank() over (partition by a order by some_date_column desc) rnk
 FROM tablename)
 WHERE rnk = 1
</code></pre>

Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the <code>ROW_NUMBER</code> or the <code>DENSE_RANK</code> analytic function rather than <code>RANK</code>.

blocks|key|3127226|text|您还可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3127227|SELECT+t.*
++FROM+
++++++++TABLENAME+t
++++JOIN
++++++++(+SELECT+A,+MAX(col_date)+AS+col_date
++++++++++FROM+TABLENAME
++++++++++GROUP+BY+A
++++++++)+m
++++++ON++m.A+=+t.A
++++++AND+m.col_date+=+t.col_date|code-block|syntax|javascript|3127228|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|$]]

You could also use:

<pre><code>SELECT t.*
 FROM 
 TABLENAME t
 JOIN
 ( SELECT A, MAX(col_date) AS col_date
 FROM TABLENAME
 GROUP BY A
 ) m
 ON m.A = t.A
 AND m.col_date = t.col_date
</code></pre>

blocks|key|2913079|text|SELECT+mu_file,+mudate
++FROM+flightdata+t_ext
+WHERE+mudate+=+(SELECT+MAX+(mudate)
+++++++++++++++++++++FROM+flightdata+where+mudate+<+sysdate)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2913080|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 mu_file, mudate
 FROM flightdata t_ext
 WHERE mudate = (SELECT MAX (mudate)
 FROM flightdata where mudate &lt; sysdate)
</code></pre>

blocks|key|2913082|text|Justin+Cave的答案是最好的，但如果你想要一个更好的选择，试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2913083|select+A,col_date
from+(select+A,col_date
++++from+tablename+
++++++order+by+col_date+desc)
++++++where+rownum<2|code-block|syntax|javascript|2913084|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|$]]

Justin Cave answer is the best, but if you want antoher option, try this: 

<pre><code>select A,col_date
from (select A,col_date
 from tablename 
 order by col_date desc)
 where rownum&lt;2
</code></pre>

blocks|key|244647|text|A是键，max(日期)是值，我们可以简化查询，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|244648|SELECT+distinct+A,+max(date)+over+(partition+by+A)
++FROM+TABLENAME|code-block|syntax|javascript|244649|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|$]]

A is the key, max(date) is the value, we might simplify the query as below:

<pre><code>SELECT distinct A, max(date) over (partition by A)
 FROM TABLENAME
</code></pre>

blocks|key|239692|text|从Oracle12C开始，您可以使用FETCH+FIRST+ROW+ONLY获取特定数量的行。在您的情况下，这意味着一个ORDER+BY，所以应该考虑性能。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|239693|SELECT+A,+col_date
FROM+TABLENAME+t_ext
ORDER+BY+col_date+DESC+NULLS+LAST
FETCH+FIRST+1+ROW+ONLY;|code-block|syntax|javascript|239694|NULLS+LAST只是以防您的字段中可能有空值。|239695|entityMap^0|I|K|1O|8|0|0|0|A|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

Since Oracle 12C, you can fetch a specific number of rows with <code>FETCH FIRST ROW ONLY</code>.
In your case this implies an <code>ORDER BY</code>, so the performance should be considered.
<pre class="lang-sql prettyprint-override"><code>SELECT A, col_date
FROM TABLENAME t_ext
ORDER BY col_date DESC NULLS LAST
FETCH FIRST 1 ROW ONLY;
</code></pre>
The <code>NULLS LAST</code> is just in case you may have null values in your field.

Let's assume I extract some set of data.

i.e.

<pre><code>SELECT A, date
FROM table
</code></pre>

I want just the record with the max date (for each value of A). I could write 

<pre><code>SELECT A, col_date
 FROM TABLENAME t_ext
 WHERE col_date = (SELECT MAX (col_date)
 FROM TABLENAME t_in
 WHERE t_in.A = t_ext.A)
</code></pre>

But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?

Taking the record with the max date

假设我提取了一组数据。即SELECT A, dateFROM table我只想要具有最大日期的记录(对于每个A值)。我可以写SELECT A, col_date  FROM TABLENAME t_ext WHERE col_date = (SELECT MAX (col_date)                  ...

问获取最大日期的记录
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取最大日期的记录EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取最大日期的记录
EN