Skip to content
Advertisement

How to get x top results for each year SQLite

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

(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:

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:

“Result: near “(“: syntax error
At line 1:
(”

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

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

Advertisement

Answer

This syntax should work:

But you can also do it with ROW_NUMBER():

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement