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
Advertisement
Answer
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