Re: rowcount > 2

On Jun 22, 12:44 pm, Mariano <mariano.calan..._at_gmail.com> wrote:
> Hi to all, then, I have this query, i would that only group with 2 or
> more row will be showed. How I can I obtain it?
>
> SELECT mal, sin, percent FROM lsa WHERE sin
> NOT IN
> (SELECT sin FROM lsa WHERE sin<>1 AND sin<>2) ORDER BY mal, sin,
> percent
>
> The result of this query is:
> MAL SIN PERCENT
> 1 1 30
> 1 2 50
> 2 1 25
> 2 2 75
> 3 1 100
>
> In this case i should not view the lastrow (3 - 1 - 100), how can I
> realize it?

SELECT mal, sin, percent
FROM lsa
WHERE sin IN (1,2)
and mal in (select mal from lsa group by mal having count(*) > 1) ORDER BY mal, sin, percent

is one way. Another would be:

with allowed as (
select mal
from lsa
group by mal
having count(*) > 1),
rows as (
select mal, sin, percent
from lsa
where sin in (1,2))
select rows.mal, rows.sin, rows.percent
from rows, allowed
where rows.mal = allowed.mal;

As I don't have time to figure out some 'really slick analytical example' these will have to do. Possibly someone else can provide an analytics example.

David Fitzjarrell Received on Fri Jun 22 2007 - 13:17:42 CDT