英文:
@query to get record with max date
问题
以下是翻译好的部分：
我有一个名为sp_500的MySQL表。每条记录都有一个唯一的日期。我想要获取表中的最后一条记录。它将具有最大的日期。如何编写最佳查询以获取这条记录？我尝试了以下的HQL查询语句：
public interface QuoteRepository extends JpaRepository<Quote, Long> {
@Query("from sp_500 a where a.date in (select max(b.date) from sp_500 b)")
Quote getQuote(String symbol);
}
但是当我运行这个查询时，显示了以下错误：
Caused by: java.lang.IllegalArgumentException:
org.hibernate.hql.internal.ast.QuerySyntaxException: sp_500 is not
mapped [from sp_500 a where a.date in (select max(b.date) from sp_500
b)]
有什么建议吗？
英文:
I have a mysql table called sp_500. Each record has a unique date. I want the last record in the table. It will have the max date. What is the best query to pull it? I tried the following HQL:
public interface QuoteRepository extends JpaRepository<Quote, Long> {
@Query("from sp_500 a where a.date in (select max(b.date) from sp_500 b)")
Quote getQuote(String symbol);
}
but when I run it, the following error is displayed:
> Caused by: java.lang.IllegalArgumentException:
> org.hibernate.hql.internal.ast.QuerySyntaxException: sp_500 is not
> mapped [from sp_500 a where a.date in (select max(b.date) from sp_500
> b)]
Suggestions?
答案1
得分: 0
可以通过两种方式使用ORDER BY来完成：
*最佳实践
您可以使用ORDER BY date desc来选择TOP 1。
从您的查询中去掉MAX部分
//如果您在使用ORACLE，我认为是ROW COUNT = 1
您可以执行SELECT * ... ORDER BY date desc，然后只获取第一条记录。
英文:
Can be done in two ways using the ORDER BY
*Best practice
You can SELECT TOP 1 with the ORDER BY date desc.
Removing the MAX part of you QUERY
//If you are using ORACLE i think is ROW COUNT = 1
You can make the SELECT * ... ORDER BY date desc
and just grab the first register
答案2
得分: 0
感谢@Nick和@luisfa19。你们给了我有价值的线索。以下是有效的部分：
@Query(value="select * from sp_500 order by date desc limit 1", nativeQuery = true)
英文:
Thanks to @Nick and @luisfa19. You gave me valuable clues. Here is what worked:
@Query(value="select * from sp_500 order by date desc limit 1", nativeQuery = true)
答案3
得分: 0
会是这样的：
Optional<Quote> findFirstByOrderByDateDesc();
英文:
It would be something like this:
Optional<Quote> findFirstByOrderByDateDesc();
专注分享java语言的经验与见解，让所有开发者获益！
评论