sql min function and other column

Question

i want to know if it is possible to add another column to a select statement that contain an aggregate function like min, max ...

example :

SELECT user_id, MAX(salary) FROM users;

is this statement correct in the sql standard(in mysql its work ); its work in mysql, but i think i read somewhere that if i put an aggregate function in the select clause, i can't put anything but an aggregate function or if there is a group by, the grouped column can be in the select clause (in mysql)

---

EDIT :

User(user_id, name, last_name, salary)

i want to select the user_id, name, (maximum salary column) from the User table; is it possible to do it without sub query?

User Table

User_id, Name, Salary

| 1 | user1 | last1 | 500  |   |
|---|-------|-------|------|---|
| 2 | user2 | last2 | 1000 |   |
| 3 | user3 | last3 | 750  |   |
|   |       |       |      |   |

the output must be the user_id, username, lastname, and salary of the user who have the max salary, so here the ouput must be :

 2 user2 last2 1000

---

Answer 1

To start with: No,

SELECT user_id, MAX(salary) FROM users;

is not standard-compliant. You are using an aggregate function (MAX) without a GROUP BY clause. By doing so you tell the DBMS to aggregate all records to one single result row. Now what do you tell the DBMS to show in this result row? The maximum salary found in the table (MAX(salary)) and the user_id. However, there is no the user_id; there are possibly many different user_id in the table. This violates the SQL standard. MySQL takes the liberty to interpret the non-aggregated user_id as any user_id (arbitrarily picked).

So even though the query runs, it's result is usually not the desired one.

This query:

SELECT user_id, name, MAX(salary) FROM users GROUP BY user_id;

on the other hand is standard-compliant. Let's see again what this query does: This time there is a GROUP BY clause telling the DBMS you want one result row per user_id. For each user_id you want to show: the user_id, the name, and the maximum salary. All these are valid expressions; the user_id is the user_id itself, the name is the one user name associated with the user_id, and the maximum salary is the user's maximum salary. The unaggregated column name is allowed, because it is functionally dependent on the grouped-by user_id. Many DBMS don't support this, though, because it can get extremely complicated to determine whether an expression is functionally dependent on the group or not.

As to how to show the user record with the maximum salary, you need a limiting clause. MySQL provides LIMIT for this, which can get you the first n rows. It doesn't deal with ties however.

SELECT * FROM users ORDER BY salary DESC LIMIT 1;

is

SELECT * FROM users ORDER BY salary FETCH FIRST ROW ONLY;

in standard SQL.

In order to deal with ties, however, as in

SELECT * FROM users ORDER BY salary FETCH FIRST ROW WITH TIES;

you need a subquery in MySQL, because LIMIT doesn't support this:

SELECT * FROM users WHERE salary = (SELECT MAX(salary) FROM users);