How to SELECT Top 1 in MySQL for last record

Question:

Good I know that in SQL Server a query like this is made to take the LAST record NOT THE FIRST

SELECT TOP 1 *
FROM Tabla
ORDER by ID DESC

So it takes me the last record of the MSSQL table, I mean SQL server 2008 R2, but in MySQL put this and it takes the first record. What should I do or what am I doing wrong? Read the beginning as I was voted negative after investigating a little more

SELECT *
FROM Tabla
ORDER by ID DESC
LIMIT 1

LEAVING Clearly that I just understood this, and I understood it after doing this Post, that the equivalent of a

TOP 1 

SQL server is

MIN(exprecion)

and the last record is

MAX(exprecion)

of course with his condition.

Answer:

It is not clear what you have done wrong. Because the 2 statements are exactly equivalent and they work perfectly fine.


TOP 1 in SQL Server:

create table tbl (
  id int not null primary key,
  texto varchar(50) not null
);

insert into tbl (id, texto) values (1, 'aaa');
insert into tbl (id, texto) values (5, 'eee');
insert into tbl (id, texto) values (2, 'bbb');
insert into tbl (id, texto) values (4, 'ddd');
insert into tbl (id, texto) values (3, 'ccc');

select top 1 *
  from tbl
 order by id desc;

Result:

id   texto
--   -----
5    eee

Demo


LIMIT 1 con MySQL

create table tbl (
  id int not null primary key,
  texto varchar(50) not null
);

insert into tbl (id, texto) values (1, 'aaa');
insert into tbl (id, texto) values (5, 'eee');
insert into tbl (id, texto) values (2, 'bbb');
insert into tbl (id, texto) values (4, 'ddd');
insert into tbl (id, texto) values (3, 'ccc');

select *
  from tbl
 order by id desc
 limit 1;

Result:

id   texto
--   -----
5    eee

Demo


As you can see, both queries work fine and return the same result.

On the other hand, the query that you put as the answer to your question:

SELECT MAX(id) *
FROM Tabla

… is not valid at all, it doesn't even run, as you can see here: Demo

Maybe you wanted to say:

SELECT MAX(t.id), t.*
FROM Tabla t

… but although at least now it runs, this query can give you completely wrong results, as you can see in the following demo:

create table tbl (
  id int not null primary key,
  texto varchar(50) not null
);

insert into tbl (id, texto) values (1, 'aaa');
insert into tbl (id, texto) values (5, 'eee');
insert into tbl (id, texto) values (2, 'bbb');
insert into tbl (id, texto) values (4, 'ddd');
insert into tbl (id, texto) values (3, 'ccc');

select max(t.id), t.*
  from tbl t;

Result:

max(t.id)  id   texto
---------  --   -----
5          1    aaa

Demo

Scroll to Top