How to get x top results for each year SQLite

Question

I've got a problem with my solution, I found this example:

(SELECT COUNT(*) FROM person AS b    
WHERE b.group = a.group AND b.age >= a.age) <= 2    
ORDER BY a.group ASC, a.age DESC

(from: Get top n records for each group of grouped results)

But I need to create new column based on a old one, so I need to make some calculation and when I'm trying to add something more I'm getting an error messages. If I simply add eaquations, it's ok, for example:

(SELECT COUNT(*) FROM person AS b   
WHERE b.group = a.group AND b.age*100 >= a.age*100) <= 2   
ORDER BY a.group ASC, a.age DESC

But when I'm trying to rename my new column, there is too many AS.

I also tried to use UNION ALL, but my SQLite is not happy with (). This is not working for me at all:

(   
  select *     
  from mytable    
  where `year` = 2012   
  order by score*100/50 AS percent desc   
  LIMIT 2   
)   
UNION ALL   
(   
  select *   
  from mytable    
  where `year` = 2013   
  order by score*100/50 AS percent desc   
  LIMIT 2   
)

"Result: near "(": syntax error
At line 1:
("

Even when I'm taking out SELECT and FROM befor the (), I'm getting the error message.

select * from mytable   
(where `year` = 2012   
  order by score*100/50 AS percent desc   
  LIMIT 2)   

UNION ALL   

select * from mytable    
(where `year` = 2013   
  order by score*100/50 AS percent desc   
  LIMIT 2)

near "WHERE": syntax error

Can please someone explain me why?

EDIT

Here is the data.

| Person | Year  | Score |   
+--------+-------+-------+
| Bob    | 2013  | 32    |   
| Jill   | 2012  | 34    |   
| Shawn  | 2012  | 42    |   
| Jake   | 2012  | 29    |   
| Paul   | 2013  | 36    |   
| Laura  | 2013  | 39    |

Desired result set:

| Person | Year  | Percent |    
+--------+-------+---------+   
| Shawn  | 2012  | 84      |   
| Jill   | 2012  | 68      |   
| Laura  | 2013  | 78      |   
| Paul   | 2013  | 72      |   
+--------+-------+---------+

where percent = score*100/50

Hi! Off course, I used the same data as in the ticket I mentionned in my description, but it's surely easier to do it with data directly in here. I've edited my description. — KamJam
The purpose is to get X (for example ten) top results for each year for a column created in the SQLite by multipling another table. — KamJam

forpas forpas · Accepted Answer · 2019-11-29T17:25:41

This syntax should work:

select * 
from (
  select *     
  from mytable    
  where `group` = 1   
  order by age desc   
  LIMIT 2   
)   
UNION ALL   
select * 
from (   
  select *   
  from mytable    
  where `group` = 2   
  order by age desc   
  LIMIT 2   
);

But you can also do it with ROW_NUMBER():

select t.`group`, t.age, ....<rest of the columns from mytable> 
from (
  select *, row_number() over (partition by `group` order by age desc) rn
  from mytable
  where `group` in (1, 2)
) t 
where t.rn <= 2

How to get x top results for each year SQLite

1 Answers