MySQL LIMIT using SELECT COUNT(*) as value within SELECT

Question

Trying to do something like this:

SELECT * FROM events WHERE
start_time >= DATE_SUB(NOW(),INTERVAL 6 HOUR)
LIMIT
(
SELECT COUNT(*) FROM events WHERE
start_time >= DATE_SUB(NOW(),INTERVAL 6 HOUR)
AND
start_time < NOW()
)+100
ORDER BY start_time ASC;

Not workIng for me though.

Trying to limit the number of results based on the number of events between 6 hours ago and now plus the 100 I'm displaying after now. — Lora Wesley
– Lora Wesley, Commented May 16, 2015 at 2:14
looks like you need to be using a union. you want all the events of the last 6 hours, plus the next 100 events, is that correct? — pala_
– pala_, Commented May 16, 2015 at 2:26
Im not sure, but I think mysql does not support subquery as an argument to LIMIT clause — Gabriel Moretti
– Gabriel Moretti, Commented May 16, 2015 at 2:28

Gordon Linoff · Accepted Answer · 2015-05-16 14:05:05Z

1

I think you are trying to do something like this:

SELECT e.*
FROM events e
WHERE e.start_time >= DATE_SUB(NOW(), INTERVAL 6 HOUR) AND
      e.start_time < NOW()
UNION ALL
(SELECT e.*
 FROM events e
 WHERE e.start_time >= NOW()
 ORDER BY e.start_time
 LIMIT 100
)
ORDER BY start_time;

You cannot use a subquery for the limit parameters.

edited May 16, 2015 at 14:05

answered May 16, 2015 at 2:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lora Wesley Over a year ago

What is this e you're using?

spencer7593 Over a year ago

The e is an alias assigned to the table reference events. The alias is assigned following the table name in the FROM clause. We use that alias to qualify column references. By adding the alias and a dot before the column name. Here e.g. e.* means all the columns in the events table. (Better practice is to qualify all column references, e.g. WHERE e.start_time . But this is only required when the column reference would be ambiguous without it. In this simple example, it's not apparent how the alias helps us much. But in more complex queries, table aliases are indispensable.

Blake A. Nichols · Accepted Answer · 2015-05-16 03:28:12Z

0

You can set a limit variable and use that.

SET @limit= (
    SELECT COUNT(*) FROM events WHERE
        start_time >= DATE_SUB(NOW(),INTERVAL 6 HOUR)
    AND
        start_time < NOW()
   )+100;

SELECT * FROM events WHERE
    start_time >= DATE_SUB(NOW(),INTERVAL 6 HOUR)
LIMIT @limit
ORDER BY start_time ASC;

answered May 16, 2015 at 3:28

Blake A. Nichols

8701 gold badge6 silver badges11 bronze badges

2 Comments

Ali Over a year ago

Hello, I am trying to do something like this but i am getting error syntax error @limit this is my query 'SET @limit= ( SELECT COUNT(dispID) FROM dispanser ); select * from counter limit @limit;'

ZenCoding Over a year ago

Same Problem for me.

Collectives™ on Stack Overflow

MySQL LIMIT using SELECT COUNT(*) as value within SELECT

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related