Selecting a Subquery

Question

I've been bugging this for the past hour..

I want to get the TOP 5 from latest data from my table clickednumbers

clickednumbers has only to columns numbers as INT & numberTime as timestamp

My query

SELECT AVG( SELECT *
            FROM clickednumbers
            ORDER BY numberTime DESC
            LIMIT 5)
FROM clickednumbers

and im always getting the error

#1064 - You have an error in your SQL syntax;check the manual that corresponds to your MariaDB server version for the right syntanx to use near 'SELECT * FROM clicked numbers ORDER BY numberTime DESC ' at line 1

MariaDB Version:Server type: MariaDB Server version: 10.1.9-MariaDB - mariadb.org binary distribution Protocol version: 10

Sorry for bothering :(

Goal : To get the average of top 5 from latest numbers based on the numbersTime

score 3 · Accepted Answer · answered Feb 18 '16 at 03:58

3

To get the average of the top 5, use a subquery in the FROM clause:

SELECT AVG(numbers)
FROM (SELECT *
      FROM clickednumbers
      ORDER BY numberTime DESC
      LIMIT 5
     ) cn;

answered Feb 18 '16 at 03:58

Gordon Linoff

1,242,037
58
646
786

score 3 · Answer 2 · edited May 23 '17 at 10:28

3

Check this out for more of an idea what's going on. I think your query needs to look more like this:

SELECT AVG(x.numbers) 
  FROM (SELECT numbers FROM clickednumbers ORDER BY numberTime DESC
  LIMIT 5) as x

edited May 23 '17 at 10:28

Community

1
1

answered Feb 18 '16 at 04:05

ObiWong

31
3

Selecting a Subquery

2 Answers2