This is the 17th day of my participation in Gwen Challenge

Author: Cola

Source: Coke’s path to data analysis

Please contact authorization for reprinting (wechat ID: data_COLA)

Writing in the front

SQL daily question is to use the SQLite library of Niuke.com topics for in-depth explanation (feel slow friends can go to brush questions), this series to start the daily plan, a daily question, progress together learning.

Topic describes

Find all the information of the newest employee. In order to ease the difficulty of entry, the date of the employee’s entry in all the data is not the same day

An example of the employees table used in this question is as follows:

CREATE TABLE 'employees' (' emp_no' int(11) NOT NULL, -- 'birth_date' 'date' NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));Copy the code

Train of thought

2. Limit the Max value of subqueries

There are some limitations to thinking 1, but this problem can also be passed by thinking 1

The answer:

select * from employees 
order by hire_date desc limit 1
Copy the code

If there is no duplicate hire_date, then the latest employee information will be retrieved. If there is no duplicate hire_date, then the latest employee information will be retrieved.

Limit n = 1 limit m = 1 limit m = 1 limit 1 = 1 order by So in this case, instead of writing limit 1, we could write limit 0,1

But in real cases, the same day does exist, so how do you do that?As shown in the figure above, I randomly inserted three pieces of data. You can see that the hire_date for EMP_no 10008 and 10009 is the same and the latest.

select * from employees 
where hire_date = (select MAX(hire_date) from employees)
Copy the code

The code above is used hereThe subquerySelect * from hire_date where (‘ hire_date ‘); select * from hire_date where (‘ hire_date ‘);