Query to return the first record with specific values

Question

I have to perform a search of the first records with a certain value, let me exemplify.

SELECT * FROM users WHERE gender LIKE '%M%' ORDER BY id DESC LIMIT 1;
SELECT * FROM users WHERE gender LIKE '%F%' ORDER BY id DESC LIMIT 1;

This returns to me the last man and the last woman who registered.

Does anyone know how I could unify these darlings? It’s because this is just an example, in my system I’m going to need to do seven of these, so if this was to unify it would be better.

I am using the Laravel, if it is possible to do this using the Eloquent would be even better.

Answer 1

A very simple way for you to unify is to do it this way

SELECT * FROM users WHERE gender LIKE '%M%' ORDER BY id DESC LIMIT 1;
UNION ALL
SELECT * FROM users WHERE gender LIKE '%F%' ORDER BY id DESC LIMIT 1;

With the unification of the two querys I believe that can give some problem in its methods that use the return of this Script, now will be returned two records, then you will have to make some modifications in its source.

You can read a little about Unions here on this website

What is the UNION?

The UNION operator combines the results of two or more queries into one single result set, returning all lines belonging to all involved in the execution. To use the UNION, the number and the column order must be identical in all queries and dates. By default, runs the equivalent of a SELECT DISTINCT in the result set end, to remove duplicate lines

What is Union All for?

The UNION ALL operator has the same functionality as UNION, but no runs SELECT DISTINCT in the final result set and displays all lines, including duplicate lines.

Answer 2

Friend there are two ways to do this. So

SELECT * FROM users WHERE gender LIKE '%M%' ORDER BY id DESC LIMIT 1
union all
SELECT * FROM users WHERE gender LIKE '%F%' ORDER BY id DESC LIMIT 1;

Or so:

select * 
  from users u 
 inner join (select gender, max(id) id FROM users group by gender) a 
    on u.id = a.id ;