Results 1 to 6 of 6

Thread: Select .....

  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Select .....

    Hi All,

    Following statement populating deptno and no_of_emps for those dept. where no_of_emps are more then 3.

    select
    deptno,count(Empno)
    from emp
    group by deptno
    having count(*)>3

    Now from this output ( say it returns 6 rows ) I want to display first 2 records .

    What I have to do for that ?

    Thanks

    JD
    Last edited by jayanta_deb; 03-11-04 at 08:20.

  2. #2
    Join Date
    Sep 2003
    Location
    Hanoi- Vietnam
    Posts
    27
    Use rownum in where clause

  3. #3
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Re: Select .....

    Originally posted by jayanta_deb
    Hi All,

    Following statement populating deptno and no_of_emps for those dept. where no_of_emps are more then 3.

    select
    deptno,count(Empno)
    from emp
    group by deptno
    having count(*)>3

    Now from this output ( say it returns 6 rows ) I want to display first 2 records .

    What I have to do for that ?

    Thanks

    JD
    try this..
    select a.deptno,a.cnt from(
    select deptno,count(empno) cnt from emp group by deptno having count(*)>2 ) a
    where rownum<3

  4. #4
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    More options

    Start with
    SQL> select * from testa;

    FIELDONE
    ----------
    first
    second
    third
    forth

    1) This will select a random 30% of records. Does not support joins.
    SQL> select * from testa sample(30.0);

    FIELDONE
    ----------
    third

    2) This can only be used to simulate a top. ie you cannot do where rownum between 20 and 30. supports joins.
    SQL> select * from testa where rownum < 3;

    FIELDONE
    ----------
    first
    second


    3) This works well and you can list any number of rows. rownum must be aliased to rowno. supports joins.
    Code:
    SQL> select * 
      2  from (select a.*, rownum rowno
      3        from testa a) u
      4  where u.rowno between 1 and 3;
    
    
    FIELDONE   ROWNO
    ----------    ----------
    first                1
    second           2
    third               3

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I want to display first 2 records
    Define "first".
    Based upon which metric?

  6. #6
    Join Date
    Jan 2004
    Posts
    153

    Re: Select .....

    Thanks to all. Your reply helps me a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •