Doubt to return a query

Question

I have the following problem, in sql server I have a table of contracts and one of clients where returns as follows:

In Where condition I need to pick up only the last created id of each customer’s contract.

Answer 1

One way to solve would be using the SQL function max with the help of function group by as a subquery of the clause IN.

Follow an example:

SELECT * FROM contrato WHERE id IN (SELECT MAX(c.id) FROM contrato c GROUP BY c.cliente);

Explaining the code

The function max Returns the maximum value in the expression (which in this case is the column id)

The group by is a clause in the SELECT statement that divides the result of the query into groups of lines, usually for the purpose of executing one or more aggregations in each group.

The logical operator IN determines whether a specified value corresponds to any value in a sub-query or a list.

This way we are first grouping the largest values of the column (id_contracts) grouped by the value of the column cliente.

By having the id's desired in a list, now just display all values of those lines. For this we use the select * external.