Improve SQL server compatibility

1 files changed, 6 insertions, 6 deletions

diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java
index f15fd113..bbf21bb0 100644
--- a/src/main/java/com/juick/service/TagServiceImpl.java
+++ b/src/main/java/com/juick/service/TagServiceImpl.java
@@ -148,7 +148,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
                 "SELECT tags.name,COUNT(messages.message_id) " +
                         "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " +
                         "AND messages.message_id=messages_tags.message_id)) " +
-                        "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC",
+                        "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id, tags.name ORDER BY tags.name ASC",
                 new TagStatsMapper(),
                 uid);
     }
@@ -165,8 +165,9 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
     @Transactional(readOnly = true)
     @Override
     public List<String> getPopularTags() {
-        return getJdbcTemplate().queryForList(
-                "select name from tags where noindex=false order by stat_messages desc limit 20", String.class);
+        return getJdbcTemplate().queryForList("""
+            select name from tags where noindex=false
+            order by stat_messages desc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""", String.class);
     }
 
     @Transactional(readOnly = true)
@@ -179,9 +180,8 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
                 AND messages.message_id=messages_tags.message_id))
                 INNER JOIN tags ON messages_tags.tag_id=tags.tag_id
                 WHERE tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore)
-                GROUP BY tags.tag_id HAVING COUNT(DISTINCT messages.user_id) > 1
-                ORDER BY cnt DESC LIMIT 20
-                    """;
+                GROUP BY tags.tag_id, tags.name HAVING COUNT(DISTINCT messages.user_id) > 1
+                ORDER BY cnt DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""";
         return getNamedParameterJdbcTemplate()
                 .query(sql, new MapSqlParameterSource()
                         .addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE),