Clever Geek Handbook

MySQL Limiting Rows by Sum

I want to make a selection and limit the number of future and previous events to 20, for example, for the newest events.

future_events

and previous_event

is 1 or 0. I could store as one column if needed.

I think I am missing the GROUP BY, but my brains are not with this this morning. This is what I have:

SELECT name, start_timestamp, end_timestamp, future_event, previous_event, url
FROM events_table
WHERE status != 'draft' AND status != 'canceled'
-- AND SUM(previous_event) <= 20
-- AND SUM(future_event) <= 20
ORDER BY start_timestamp DESC

-- Sample Table
CREATE TABLE IF NOT EXISTS `events_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `url` varchar(500) NOT NULL,
  `start_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` varchar(10) NOT NULL,
  `future_event` tinyint(1) NOT NULL,
  `previous_event` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- Data
-- Each row is an event with a unique event, time, url etc.

Expected Result

  • Returns a maximum of 40 results in total
  • Max. 20 Future events (where future_event

    = 1)
  • Max. 20 Previous Events (Where previous_event

    = 1)
  • Shows only the most recent events
+3


source to share


2 answers


You can use below -



SELECT a.* FROM ((SELECT `name`, start_timestamp, end_timestamp, future_event, previous_event, url
FROM events_table
WHERE STATUS != 'draft' AND STATUS != 'canceled' AND previous_event = 1 
ORDER BY start_timestamp DESC LIMIT 20) 
UNION 
(SELECT `name`, start_timestamp, end_timestamp, future_event, previous_event, url
FROM events_table
WHERE STATUS != 'draft' AND STATUS != 'canceled' AND previous_event = 1 
ORDER BY start_timestamp DESC LIMIT 20)) a ORDER BY start_timestamp DESC;
0


source


You can use UNION and do two queries:



((SELECT `name`,
   start_timestamp,
   end_timestamp, 
   future_event, 
   previous_event, url
 FROM events_table
   WHERE STATUS != 'draft' 
   AND STATUS != 'canceled' 
   and future_event = 1
   ORDER BY start_timestamp DESC limit 20)

  UNION

  (SELECT `name`,
          start_timestamp,
          end_timestamp, 
          future_event,
          previous_event,
          url
       FROM events_table
       WHERE STATUS != 'draft'
         AND STATUS != 'canceled'
         and previous_event = 1
       ORDER BY start_timestamp DESC limit 20))
0


source






More articles:


All Articles