Conditions on dates in MYSQL know which last record and penultimate

Asked

Viewed 36 times

2

This is the following I need to perform a query in my database for the last registration date and where the featured is = to 0. Run this way smoothly: 

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 in finding the penultimate registered record, 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 answer:

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 registration 229 turns up featured=1, the visualization 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 has featured=1, both in SQL1 and SQL2 the returned record is 220, the sure would be that the 220 was the Last record and the 227 the Penultimate Record by the registration date. The table below follows 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     |
+-----+---------------------+----------+
mysql

1 answer

0


You could not run a single query to return the two values you want, instead of having q make two queries and all these calculations??

Something like that wouldn’t solve your problem:

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

This way you sort the dates in descending order, search only for featured that are like 0, and limit to 2 single results. Thus returns the last and last record q vc goes down at once.

  • I appreciate your attention and really a simplified view of the problem, I was involved by what the client was needing and asking, and I had no way to observe the solution. Thank you again for your time away from me, good weekend, my friend.

  • rsrs is sometimes the solution is so simple that we do not realize rsrs, if this solved your problem, just ask to signal the answer as chosen and from a little vote up to help the friend here kkk Hugs and good weekend for you tbm.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.