Query to return the first record with specific values

Navigation
1

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 gives me the last man and the last woman to join.

Does anyone know how I could unify these queries? It's because this is just an example, in my system I will need to do some 7 of these, so if you could unify it would be better.

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

    
asked by anonymous 02.10.2018 / 18:51

2 answers

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 ;
    
02.10.2018 / 19:15
2

A very simple way you can unify is by doing this 

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 queries I believe that it may give some problem in their methods that use the return of this script, since now two records will be returned, so you will have to make some modifications in your source.

You can read a little about UNIONs here on this site

What is UNION?

  

The UNION operator combines the results of two or more queries into one   single result set, returning all rows belonging to all   involved in the implementation. To use UNION, the number and   order of the columns must be identical on all queries and date.   By default, it performs the equivalent of a SELECT DISTINCT in the result set   end, to remove duplicate rows

What is UNION ALL for?

  

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

    
02.10.2018 / 19:03
Calculation of interest and penalties in PHP Catch custom data-attribute attribute within a loop