at first here is the alpha version of what I want: http://sqlfiddle.com/#!2/45c89/2
However I don't want to count all representative_id, but only this rows with the lowest id, eg:
(`id`, `economy_id`, `representative_id`)
(1, 1, 5), <-this one, lowest id through the same economy_id=1
(2, 1, 6),
(3, 1, 7),
(4, 1, 8),
(5, 1, 3),
(6, 1, 4),
(7, 1, 1),
(8, 1, 2),
(9, 1, 102),
(10, 2, 7), <-this one, lowest id through the same economy_id=2
(11, 2, 8),
(12, 2, 102),
(13, 2, 1),
(14, 2, 2),
(15, 2, 3),
(16, 2, 4),
(17, 3, 3), <-this one, lowest id through the same economy_id=3
(18, 3, 4),
(19, 3, 1),
(20, 3, 2),
(21, 3, 102),
(22, 4, 1), <-this one, lowest id through the same economy_id=4
(23, 4, 2),
(24, 4, 102),
(25, 5, 1), <-this one, lowest id through the same economy_id=5
(26, 5, 2),
(27, 5, 102),
(28, 5, 7),
(29, 6, 1), <-this one, lowest id through the same economy_id=6
The output should be:
representative_id, count()
According to above example:
5, 1
7, 1
3, 1
1, 3
Is it possible only in SQL?
If I'm understanding your question correctly, I think this should work using min in a subquery and joining back to itself:
select s.representative_id, count(*)
from stl_parliament s
join
(
select min(id) minid
from stl_parliament
group by economy_id
) t on s.id = t.minid
group by s.representative_id
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments