How to make SELECT Top 1 in MySQL for last registration

Question

How to make SELECT Top 1 in MySQL for last registration

Navigation

#1 by (6 votes)
#2 by (0 votes)

4

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

SELECT TOP 1 *
FROM Tabla
ORDER by ID DESC

This is how I get the last MSSQL table record, SQL server 2008 R2, but in MySQL I put this one and it takes me the first record What should I do or what am I doing wrong? Read the beginning since I voted negative after investigating a little more

SELECT *
FROM Tabla
ORDER by ID DESC
LIMIT 1

LEAVING in Claro that I have tried to understand this, and I understood it after doing the Post this, that the equivalent of a

TOP 1

of SQL server is

MIN(exprecion)

and the last record is

MAX(exprecion)

Of course it is with its condition.

mysql mysqli

asked by Juan Carlos Villamizar Alvarez 01.01.2017 в 18:06

source

2 answers

0

As I see you are taking the descending order and that would take the last record entered, taking into account that the id is self-increasing. The query would look something like this:

SELECT * FROM Tabla ORDER by ID ASC LIMIT 1

This would bring you the oldest record. But keep in mind how the ID field grows

answered by 02.01.2017 в 00:16

Problem with recursive multidimensional matrix Using the macro __LINE__ - C

score 6 · Accepted Answer

It is not clear what you have done wrong. Because the 2 sentences are exactly equivalent and work perfectly well.

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 with 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 well 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 does not 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 erroneous 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