MySQL How can I choose to receive a specific status, as well as get the last identifier, if it does not match the status of the requirement?

Question

I have seen solutions for something similar on other posts, but I have a problem related to my problem.

I have this table for the latest story voucher:

t_release:

+------------+---------+----------------+-------------------+ | release_id | code_id | code_status_id | code_created_date | +------------+---------+----------------+-------------------+ | 1 | 32 | 2 | 4/28/2016 8:54 | | 1 | 32 | 2 | 4/28/2016 8:54 | | 2 | 32 | 3 | 4/28/2016 8:55 | | 3710 | 32 | 2 | 6/18/2016 10:20 | | 4 | 33 | 2 | 4/28/2016 9:54 | | 5 | 33 | 2 | 4/28/2016 10:54 | | 3711 | 33 | 2 | 6/18/2016 11:20 | | 6 | 34 | 2 | 4/28/2016 11:54 | | 7 | 34 | 3 | 4/28/2016 0:54 | | 3712 | 34 | 2 | 6/18/2016 0:20 | +------------+---------+----------------+-------------------+

and

r_code_status:

+----------------+-------------+ | code_status_id | code_status | +----------------+-------------+ | 1 | Available | | 2 | Requesting | | 3 | Paid | +----------------+-------------+

When I run:

SELECT 
t1.release_id,
t1.code_id,
t1.code_status_id,
t1.code_created_date
FROM t_release t1
LEFT JOIN t_release t2 ON t1.code_id = t2.code_id AND t1.release_id < t2.release_id
WHERE ISNULL(t2.release_id)

+------------+---------+----------------+-------------------+ | release_id | code_id | code_status_id | code_created_date | +------------+---------+----------------+-------------------+ | 3710 | 32 | 2 | 6/18/2016 10:20 | | 3711 | 33 | 2 | 6/18/2016 11:20 | | 3712 | 34 | 2 | 6/18/2016 0:20 | +------------+---------+----------------+-------------------+

I need if code_id matches code_status_id = '3'or 'Paid', the request can get it as the last story, otherwise if code_ids code_status_id = '2', then id(release_id).

I want to get the result as follows:

+------------+---------+----------------+-------------------+ | release_id | code_id | code_status_id | code_created_date | +------------+---------+----------------+-------------------+ | 2 | 32 | 3 | 4/28/2016 08:55 | | 3711 | 33 | 2 | 6/18/2016 11:20 | | 7 | 34 | 3 | 4/28/2016 0:54 | +------------+---------+----------------+-------------------+

DDL:

create table t_release (
release_id INTEGER(11) NOT NULL AUTO_INCREMENT,
code_id INTEGER(11) DEFAULT NULL,
code_status_id TINYINT(4) DEFAULT NULL,
code_created_date DATETIME DEFAULT NULL,
PRIMARY KEY (`release_id`)
) ENGINE=InnoDB
AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8';

INSERT INTO t_release
(`release_id`,`code_id`,`code_status_id`,`code_created_date`)
VALUES
(1, '32', '2', '2016-4-28 8:54'),
(2, '32', '3', '2016-4-28 8:55'),
(3710, '32', '2', '2016-6-18 10:20'),
(4, '33', '2', '2016-4-28 9:54'),
(5, '33', '2', '2016-4-28 10:54'),
(3711, '33', '2', '2016-6-18 11:20'),
(6, '34', '2', '2016-4-28 11:54'),
(7, '34', '3', '2016-4-28 0:54'),
(3712, '34', '2', '2016-6-18 0:20');

and this is sqlfiddle link: http://sqlfiddle.com/#!9/87843

Answer 1

SELECT * FROM t_release t where release_id IN 
    (SELECT DISTINCT release_id FROM t_release WHERE code_status_id = 3)
UNION ALL
SELECT * FROM t_release t where release_id IN 
    (SELECT MAX(release_id) FROM t_release x WHERE 
    NOT EXISTS (SELECT 1 FROM t_release y WHERE code_status_id = 3 and y.code_id = x.code_id))
   GROUP BY code_id)

, , release_ , .

Answer 2

, MySQL user defined variables

SELECT 
t.release_id,
t.code_id,
t.code_status_id,
t.code_created_date
FROM 
(
  SELECT 
  *,
  IF(@sameCodeID = code_id, @rn := @rn + 1,
     IF(@sameCodeID := code_id, @rn := 1, @rn := 1)
   ) AS row_number
  FROM t_release t
  CROSS JOIN (SELECT @sameCodeID := 0, @rn := 1) AS var
  ORDER BY code_id, code_status_id DESC, release_id DESC
) AS t
WHERE t.row_number <= 1
ORDER BY t.code_id

---