Select only first two rows in a group
Johan Badenhorst
sum(Score) as sm
from table
group by name
order by sm desc
person1 80
person1 70
person1 50
person2 88
person2 84
person2 83
person2 70
person3 90
person4 56
person4 45
Mark Rotteveel
> I use the following SQL to add the scores of each person in the table
> below together:
>
> Select name
> sum(Score) as sm
> from table
> group by name
> order by sm desc
>
> However, I want to limit it to adding only the first two rows of each
> person. Is there a way to limit that in the group by section?
Mark
--
Mark Rotteveel
Johan Badenhorst
Mark Rotteveel
> I am using Firebird 2.5.9
write a stored procedure to only emit or sum the first two rows per person.
For example (as an EXECUTE BLOCK, this will not allow you to sort by
highest total score though):
execute block returns (NAME type of column NAME_SCORE.NAME, SM bigint)
as
declare SCORE type of column NAME_SCORE.SCORE = 0;
declare CURRENT_NAME type of column NAME_SCORE.NAME;
declare CURRENT_NAME_COUNT integer = 0;
begin
for select NAME, SCORE from NAME_SCORE order by NAME, SCORE desc
into CURRENT_NAME, SCORE
do
begin
if (CURRENT_NAME is distinct from NAME) then
begin
if (NAME is not null) then suspend;
CURRENT_NAME_COUNT = 0;
NAME = CURRENT_NAME;
SM = 0;
end
CURRENT_NAME_COUNT = CURRENT_NAME_COUNT + 1;
if (CURRENT_NAME_COUNT <= 2) then
begin
SM = SM + SCORE;
end
end
if (SM <> 0) then SUSPEND;
end
It might be possible to do something with a recursive CTE in Firebird
2.5.9 to select the first two rows of each group, but to be honest,
those are not really my forte.
In Firebird 3.0 or higher, you can use window functions. Specifically,
you can use ROW_NUMBER() to number rows, and the sum only those with a
number less than 2.
For example:
select NAME, SUM(SCORE) as SM
from (
select NAME, SCORE, row_number() over (partition by NAME order by
SCORE desc) as RN
from NAME_SCORE
)
where RN <= 2
group by NAME
order by SM desc
Mark
--
Mark Rotteveel
Svein Erling Tysvær
from table t1
join table t2 on t1.name = t2.name
and ( t1.score > t2.score // t1 contains the highest score
or ( t1.score = t2.score
and t1.id > t2.id ) ) // and if score is equal, the highest id
where not exists( select *
from table t3
where t2.name = t3.name
and t3.id not in (t1.id, t2.id )
and ( t2.score < t3.score // t3 shouldn't be higher than t2
or ( t2.score = t3.score
and t3.id > t2.id // if equal prefer the one with highest ID
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/9be8c601-8d7d-cb61-f23f-6aab7b483012%40lawinegevaar.nl.
Omacht András
Hi Johan,
CREATE TABLE TEST (
N VARCHAR(10),
V INTEGER
);
INSERT INTO TEST (N, V)
VALUES ('Person1', 80);
INSERT INTO TEST (N, V)
VALUES ('Person1', 70);
INSERT INTO TEST (N, V)
VALUES ('Person1', 50);
INSERT INTO TEST (N, V)
VALUES ('Person2', 88);
INSERT INTO TEST (N, V)
VALUES ('Person2', 84);
INSERT INTO TEST (N, V)
VALUES ('Person2', 83);
INSERT INTO TEST (N, V)
VALUES ('Person2', 70);
INSERT INTO TEST (N, V)
VALUES ('Person3', 90);
INSERT INTO TEST (N, V)
VALUES ('Person4', 56);
INSERT INTO TEST (N, V)
VALUES ('Person4', 45);
CREATE DESCENDING INDEX TEST_IDX1 ON TEST (N, V);
with
persons as (
select distinct tp.n pers
from test tp)
select p.pers,
coalesce((select first 1 t1.v
from test t1
where t1.n = p.pers
order by t1.v desc), 0) +
coalesce((select first 1 skip 1 t1.v
from test t1
where t1.n = p.pers
order by t1.v desc), 0)
from persons p
PERS ADD
Person1 150
Person2 172
Person3 90
Person4 101
or, if your table is big enough and distinct natural read is too slow you need an another (ascending) index:
CREATE INDEX TEST_IDX2 ON TEST (N);
and the query is:
with recursive persons as (
select tx1.n pers
from (select first 1 t1.n
from test t1
where t1.n > ''
order by t1.n) tx1
union all
select
(select first 1 t2.n
from test t2
where t2.n > persons.pers
order by t2.n)
from persons
where persons.pers is not null)
select p.pers,
coalesce((select first 1 t1.v
from test t1
where t1.n = p.pers
order by t1.v desc), 0) +
coalesce((select first 1 skip 1 t1.v
from test t1
where t1.n = p.pers
order by t1.v desc), 0)
from persons p
where p.pers is not null
András
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/b6ff52f5-383d-4ae2-ba21-994af925b4ben%40googlegroups.com.