Help with select in MYSQL

Question

I would like to know how I do to list for each city the oldest clients, tried several commands but without success. I would like you to quote automatically. Below some commands I used, however I did not get the expected result.

select distinct address,name from client order by anoIngresso asc;

with this command he returns me all the addresses, what I wanted is that for the city A I only get the most antido client from her and so on.

Example:

city A -- customer Joao

City B -- maria customer

city C -- customer Jose

.....

I’d appreciate it if someone could help me...

Answer 1

You can give a select sorting by the two columns you wish to use as a criterion. If I have understood correctly, you wish to give this select on the table Cliente. That way it would look like this:

SELECT cidade, nome
FROM Cliente
ORDER BY anoIngresso ASC;

To make sure the field anoIngresso is of the table Cliente, you can try like this:

SELECT cidade, nome
FROM Cliente
ORDER BY Cliente.anoIngresso ASC;

In both cases, it will take (and show) the fields cidade and nome, ordering them through the field anoIngresso, that is, from the oldest, to the most recent.

And if you want to set a limit for up to a specific year, you can add the WHERE thus:

SELECT cidade, nome
FROM Cliente
ORDER BY anoIngresso ASC
WHERE anoIngresso < 2000;

That is, show the cidade and the nome of customers who joined before the year 2000, ordering them by anoIngresso.

[Edited]

The "problem" of SELECT DISTINCT with two fields, is that it takes into account, obviously, the two fields. Ie if I have a table Funcionarios thus:

id   |   nome   |     cidade       | idade
1    |  João    |  Rio de Janeiro  |   30
2    |  Maria   |  Rio de Janeiro  |   27
3    |  João    |  São Paulo       |   28

And then execute:

SELECT DISTINCT cidade, nome FROM Funcionarios ORDER BY idade ASC;

Will return:

    cidade     | nome
Rio de Janeiro | Maria
São Paulo      | João
Rio de Janeiro | João

For, "Rio de Janeiro and Maria" and "Rio de Janeiro and João" sane distinguished; as well as "Saint Paul and John" and "Rio de Janeiro and João" sane distinguished.

So, if you want there to be only one client for each city, I think you will have to manipulate the data after the query, because only through Mysql (as far as I knew) it is not possible to get the desired result.

I hope I’ve helped!

Answer 2

To solve the problem you should run the following command

select * from cliente where cidade = [ entre " " coloque o nome da cidade ] ORDER BY anoIngresso DESC LIMIT [ aqui você coloca o quanto que deseja que apareça ]

Answer 3

as I do to list for each city the oldest customers

As the structure of your table was not posted, I will suggest the following:

CREATE DATABASE `teste` DEFAULT CHARSET `utf8` DEFAULT COLLATE `utf8_unicode_ci`;
USE `teste`;
CREATE TABLE `clientes` (
    `idCliente` int(11) NOT NULL AUTO_INCREMENT,
    `nome` varchar(255) NOT NULL DEFAULT '',
    `endereco` varchar(255) NULL DEFAULT NULL,
    `cidade` varchar(255) NULL DEFAULT NULL,
    `anoIngresso` smallint(6) NOT NULL DEFAULT 0,
    PRIMARY KEY(`idCliente`)
) ENGINE=InnoDB;

In order to test, I will enter some records. Note that the client that should be returned contains an asterisk at the end of the name:

INSERT INTO `clientes` (`nome`, `endereco`, `cidade`, `anoIngresso`) VALUES
    ('João *', 'Rua A', 'Rio de Janeiro', 1992),
    ('José', 'Rua D', 'Rio de Janeiro', 1995),
    ('Joaquim', 'Rua H', 'Rio de Janeiro', 1997),
    ('Maria', 'Rua H', 'São Paulo', 2000),
    ('Janete', 'Rua 1', 'São Paulo', 1995),
    ('Malaquias *', 'Rua E', 'São Paulo', 1992),
    ('Zeca *', 'Rua X', 'Curitiba', 1997),
    ('Inácio', 'PF', 'Curitiba', 2018);

Finally, we will execute the query-solution:

SELECT `cl`.*
    FROM `clientes` AS `cl`
INNER JOIN (
    SELECT `cidade`, MIN(`anoIngresso`) AS `menorAno`
    FROM `clientes`
    GROUP BY `cidade`
) AS `cd`
    ON `cl`.`cidade` = `cd`.`cidade` and `cl`.`anoIngresso` = `cd`.`menorAno`;

Returning:

+-----------+-------------+----------+----------------+-------------+
| idCliente | nome        | endereco | cidade         | anoIngresso |
+-----------+-------------+----------+----------------+-------------+
|         1 | João *      | Rua A    | Rio de Janeiro |        1992 |
|         6 | Malaquias * | Rua E    | São Paulo      |        1992 |
|         7 | Zeca *      | Rua X    | Curitiba       |        1997 |
+-----------+-------------+----------+----------------+-------------+
3 rows in set (0.00 sec)

Example:

city A -- customer Joao

City B -- maria customer

city C -- customer Jose

Simply change the filter of the query:

SELECT `cl`.`cidade`, `cl`.`nome`
    FROM `clientes` AS `cl`
INNER JOIN (
    SELECT `cidade`, MIN(`anoIngresso`) AS `menorAno`
    FROM `clientes`
    GROUP BY `cidade`
) AS `cd`
    ON `cl`.`cidade` = `cd`.`cidade` and `cl`.`anoIngresso` = `cd`.`menorAno`;

Returning:

+----------------+-------------+
| cidade         | nome        |
+----------------+-------------+
| Rio de Janeiro | João *      |
| São Paulo      | Malaquias * |
| Curitiba       | Zeca *      |
+----------------+-------------+
3 rows in set (0.00 sec)

A similar question has been solved here:

Sopt - How to get the value of a column corresponding to the maximum of another column?