Conditions on dates in MYSQL know the last record and penultimate

Navigation
2

This is the following I need to perform a query in my database by the last date of registration and where the featured is = to 0. I execute this way without problems: 

SQL1 = SELECT id, created, featured FROM roexd_content WHERE created = (SELECT MAX(created) FROM roexd_content WHERE featured=0) ORDER BY created DESC LIMIT 1

My difficulty is to look for the penultimate registered register, my sql for the penultimate is like this: 

SQL2 = SELECT id, created, featured FROM roexd_content WHERE created <= (SELECT MAX(created) FROM roexd_content WHERE featured=0) AND id <= (SELECT MAX(id) FROM roexd_content WHERE id <= (SELECT MAX(id)-1 FROM roexd_content WHERE featured=0)) ORDER BY created DESC LIMIT 1

I get the following response: 

1- Último Registro SQL1 / id -> 229 | created -> 2017-09-27 15:14:00 | featured -> 0|    
2- Penúltimo Registro SQL2 / id -> 228 | created -> 2017-09-27 12:37:00 | featured -> 0|

Now if record 229 becomes featured=1 , the view is as follows: 

1- Último Registro SQL1 / id -> 228 | created -> 2017-09-27 12:37:00 | featured -> 0|
2- Penúltimo Registro SQL2 / id -> 220 | created -> 2017-09-27 03:09:00 | featured -> 0|

The problem is if the 228 happens to have featured = 1, both in SQL1 and SQL2 the returned record is 220, the right would be that the 220 was the Last record and the 227 the Penultimate Record by the date of registration. The table below is the basis of my information: 

+--------------------------------------+
| id  | created             | featured |
+-----+---------------------+----------+
| 201 | 2017-09-14 02:34:00 |    0     |
| 202 | 2017-09-14 02:40:32 |    0     |
| 203 | 2017-09-14 02:52:00 |    0     |
| 204 | 2017-09-14 02:58:00 |    0     |
| 205 | 2017-09-14 03:15:08 |    0     |
| 206 | 2017-09-14 16:37:00 |    0     |
| 207 | 2017-09-20 01:13:00 |    0     |
| 208 | 2017-09-15 15:36:00 |    0     |
| 209 | 2017-09-15 15:57:00 |    0     |
| 210 | 2017-09-19 22:14:00 |    0     |
| 211 | 2017-09-18 22:19:00 |    0     |
| 212 | 2017-09-18 22:27:00 |    0     |
| 213 | 2017-09-18 22:33:00 |    0     |
| 214 | 2017-09-20 01:46:00 |    0     |
| 215 | 2017-09-20 02:10:00 |    0     |
| 216 | 2017-09-21 01:50:00 |    0     |
| 217 | 2017-09-21 02:01:00 |    0     |
| 218 | 2017-09-21 02:21:00 |    0     |
| 219 | 2017-09-22 03:04:00 |    0     |
| 220 | 2017-09-27 03:09:00 |    0     |
| 221 | 2017-09-22 03:17:00 |    0     |
| 222 | 2017-09-22 03:32:00 |    0     |
| 223 | 2017-09-22 03:43:00 |    0     |
| 224 | 2017-09-22 11:45:00 |    0     |
| 225 | 2017-09-26 03:24:14 |    0     |
| 226 | 2017-09-26 03:33:00 |    0     |
| 227 | 2017-09-26 20:47:00 |    0     |
| 228 | 2017-09-27 12:37:00 |    0     |
| 229 | 2017-09-27 15:14:00 |    0     |
+-----+---------------------+----------+
    
asked by anonymous 29.09.2017 / 21:34

1 answer

0

You could not execute a single query to return the two values you want, instead of having to do two queries and all of those calculations?

Something like this would not solve your problem:

SELECT * FROM roexd_content WHERE featured=0 ORDER BY created DESC LIMIT 0,2;

That way you sort the dates in descending order, only by featured that are as 0, and limits to 2 unique results. So return the last and the last record that you leave at one time.

    
30.09.2017 / 02:34
Problems with improper access to urls in the application with Spring Security Get an XML list in JAVA