Re: rowcount > 2

On Jun 22, 2:17 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

Nice examples.

Analytical:
SELECT
  MAL,
  SIN,
  PERCENT
FROM
  (SELECT
    MAL,
    SIN,
    PERCENT,
    ROW_NUMBER() OVER (PARTITION BY SIN ORDER BY MAL) RN   FROM
    LSA)
WHERE
  RN<=2;

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jun 22 2007 - 14:24:43 CDT