Geek Answers Handbook

MySQL ignores NULL results after first occurrence

I have a lastviewed table in a MySQL database,

 CREATE TABLE `lastviewed` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `record_id` int(11), `product_id` int(11) NOT NULL DEFAULT '0', `user_id` int(11) NOT NULL, `lastviewed` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I need to get the last 4 rows of ORDER BY lastviewed DESC LIMIT 4 this table, filtered by the current [registered] user_id , and this is not very important, but there are some new requirements that I'm stuck in

I have the following cases.

  • DO NOT show the NULL record row if this product already has another row (in the first 4 records of the last scan, because only the first 4 are shown) with the record (i.e. the record_id column has a value).
  • Show the NULL line only if the product does not have another line with the record_id value in one of the first four lines of the last scan, since only the first 4 are shown.
  • If for the same product there are two, three or four lines, and all lines have different record_id , they are all displayed.

I currently have this request

 SELECT * FROM `lastviewed` WHERE `user_id` = xxx ORDER BY `lastviewed` DESC LIMIT 4

I know that we need some sub-queries and IF / ELSE CASE / THEN conditions, but I don’t know how to do this.

Data examples

 id record_id product_id user_id lastviewed 261766 145304 95650 266 2014-03-14 03:34:16 261594 NULL 95650 266 2014-03-14 02:47:38 261765 145303 91312 266 2014-03-14 01:57:26 261444 NULL 91312 266 2014-03-14 01:44:33 261778 145314 91312 266 2014-03-10 23:02:39 261777 NULL 91312 266 2014-03-10 23:02:13 261776 145313 91312 266 2014-03-10 23:00:26 261775 NULL 91312 266 2014-03-10 22:59:13 261774 NULL 93185 266 2014-03-10 22:57:16 261773 NULL 93185 266 2014-03-10 22:53:47

And the desired result

 261766 145304 95650 266 2014-03-14 03:34:16 261765 145303 91312 266 2014-03-14 01:57:26 261778 145314 91312 266 2014-03-10 23:02:39 261776 145313 91312 266 2014-03-10 23:00:26 261774 NULL 93185 266 2014-03-10 22:57:16

WITH LIMIT 4

 261766 145304 95650 266 2014-03-14 03:34:16 261765 145303 91312 266 2014-03-14 01:57:26 261778 145314 91312 266 2014-03-10 23:02:39 261776 145313 91312 266 2014-03-10 23:00:26

Fiddle: http://sqlfiddle.com/#!2/3e20e/2/0

+6
source share
1 answer

I hope this helps and lives up to your expectations.

 SELECT * FROM (SELECT * FROM `lastviewed` WHERE `user_id` = 266 AND record_id IS NOT NULL UNION SELECT * FROM `lastviewed` WHERE product_id NOT IN (SELECT product_id FROM `lastviewed` WHERE record_id IS NOT NULL GROUP BY product_id) ) AS q1 GROUP BY product_id, record_id ORDER BY `lastviewed` DESC LIMIT 4;
+1
source

More articles:


All Articles