CODEWITHSUNDEEP
mysqlsql
zzlalani
zzlalani

Reputation: 24374

MySQL Ignore NULL results after first occurrence

I have a table lastviewed in 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 last 4 rows ORDER BY lastviewed DESC LIMIT 4 of this table filtered by current [logged-in] user_id and this is not the big deal, But there are some new requirements where I'm stuck

I've following cases.

  1. DON'T show the NULL record row if that product already has another row (in the first 4 entries of last viewed. because only the first 4 are shown) with a record (i.e. record_id column has a value).
  2. Show the NULL row only if the product does not have another row with a value record_id in one of the first 4 rows of the last viewed because only first 4 shown.
  3. If there are two, three or four rows for the same product and all rows have different record_id show all of them.

Currently I've this query

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

I know we required some sub queries and IF/ELSE CASE/THEN conditions, but I've NO HINT how to achieve it.

Sample Data

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 required 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 the 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

Upvotes: 6

Views: 297

Answers (1)

mca
mca

Reputation: 66

I hope it helps and meets 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;

Upvotes: 1

Related Questions