CODEWITHSUNDEEP
mysqlsqlhsqldbjdbctemplate
orly.sharon
orly.sharon

Reputation: 193

Query gets "invalid order by" when using hsql db while Mysql does not ( result must be one column only)

While this query is running fine in a MySQL database:

SELECT DISTINCT user_id  
FROM table_name 
WHERE user_name IN (:userNameList) 
  AND user_type IN ('client','vip')   
ORDER BY user_login_time DESC
LIMIT 10  OFFSET 0

When I'm trying to run the same query via HSQL, I'm getting this error:

nested exception is java.sql.SQLSyntaxErrorException: invalid ORDER BY expression in statement [SELECT DISTINCT user_id FROM table_name WHERE user_name IN (:userNameList) AND user_type IN ('client','vip') ORDER BY user_login_time desc LIMIT 10 OFFSET 0]

Upvotes: 1

Views: 237

Answers (2)

fredt
fredt

Reputation: 24372

A query like this returns only the user_id COLUMN

SELECT user_id FROM 
  ( SELECT DISTINCT user_id, user_login_time  
    FROM table_name 
    WHERE user_name IN (:userNameList) 
    AND user_type IN ('client','vip')   
    ORDER BY user_login_time DESC
    LIMIT 10  OFFSET 0 )

If a user has several logins, the MySQL query returns one row for the user with one of the login times used for ordering, which may not be the last login time for the user.

It is better to use GROUP BY to ensure consistent results:

SELECT user_id 
FROM table_name 
WHERE user_name IN (:userNameList) 
  AND user_type IN ('client','vip')   
GROUP BY user_id
ORDER BY MAX(user_login_time) DESC
LIMIT 10  OFFSET 0

Upvotes: 1

orly.sharon
orly.sharon

Reputation: 193

The solution for this problem requires me to change the query: in HSQL we must select the order by column. without it, we are getting this error.

SELECT DISTINCT user_id, user_login_time  
FROM table_name 
WHERE user_name IN (:userNameList) 
  AND user_type IN ('client','vip')   
ORDER BY user_login_time DESC
LIMIT 10  OFFSET 0

But this solution returns two columns instead of one column.

By using JDBCTAmplate, I found a way to return only one column

String query = "SELECT DISTINCT user_id , user_login_time  FROM table_name WHERE user_name IN (:userNameList) AND user_type IN ('client','vip') ORDER BY user_login_time DESC LIMIT 10  OFFSET 0";
    
List<String> userIds = new ArrayList<String>();
List<String> userIdList = List.of("12345667", "123443235");
Map<String, Object> sqlParameterSource = new HashMap<>();
sqlParameterSource.put("userNameList", userIdList);
                
userIds = namedParamJDBCTemplate.query(query, new MapSqlParameterSource(sqlParameterSource), new ResultSetExtractor<List>() {
                 
                    @Override
                    public List extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                        List<String> listRes = new ArrayList<String>();
                        while (resultSet.next()) {
                            listRes.add(resultSet.getString(USER_ID));
                        }
                        return listRes;
                    }
                });

This solution allows me to run the query via HSQL and still getting only one column as a result.

Upvotes: 1

Related Questions