In my database I've got the table "article" with a column named "price" and another column named "name" and several others that are not relevant right now. The task is to get the most expensive article by using SQL with a nested select in this way:

Code: Select all

SELECT article.name
FROM article
WHERE article.price >= all (SELECT article.price FROM article)

Kexi says that the query is incorrect because of "SELECT" being a reserved keyword. Is there anything wrong with the query? Does Kexi support SQL subqueries? How?
Thanks!

Hi Octribin
Thanks for using Kexi.

The app has simple support for SQL, own SQL dialect, common for many backends.
It does not handle subqueries at the moment. When it will, queries will be also available for inserting in the visual Design view of the Designer.

This particular task can be implemented using:

SELECT article.name FROM article ORDER BY article.price DESC LIMIT 1

Unfortunately Kexi does not yet allow the "LIMIT" option (nor "SELECT TOP") so all can be done for now would be naive but correct:

SELECT article.name FROM article ORDER BY article.price DESC

First name on the list is the one with the maximum price. Add article.price to SELECT to also see the prices.

Lastly, the ALL operator (as in https://www.w3schools.com/sql/sql_any_all.asp) isn't supported currently.

(Report for any missing feature would be welcome: https://community.kde.org/Kexi/File_a_bug_or_wish)