1

Get other columns that correspond with MAX value of one column?

I know that when SELECT, sql usually gives all rows from the table(without WHERE condition).

but why this time sql gives data of only the first row for other columns(video_category, url, ...)?

does MAX() changes the behavior of SELECT?

if so, why the rest of columns are not derived from row of MAX(id)?

Jerry
  • 399
  • 1
  • 2
  • 17
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Mar 12 '18 at 08:58

1 Answers1

2

If you want all columns from the record having the max id, then you will have to use a subquery:

SELECT *
FROM yourTable
WHERE id = (SELECT MAX(id) FROM yourTable);

Your current output is only one record because when say take the max over the entire table, you are no longer speaking of individual records. I am guessing that you are using MySQL, in which case the values you see for the other columns were chosen by the database. But there is no guarantee about which record was chosen.

Some versions of SQL (e.g. MySQL, SQL Server), support LIMIT/TOP functionality which might simplify things. For example, on SQL Server we can just write:

SELECT TOP 1 *
FROM yourTable
ORDER BY id DESC;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Oracle has finally adopted [row limiting](https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABBADDD) as well since 12: `SELECT * FROM yourTable FETCH FIRST 1 ROW ONLY` – Jiri Tousek Mar 12 '18 at 07:46