Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    50

    Unanswered: SELECT just a specified number of entries...

    Hi! I have a SQL query that gets me the complete top of music listeners

    Code:
    SELECT nick, avatar, SUM(nTimes)
    FROM RadioUser NATURAL JOIN listenedBy NATURAL JOIN releasedBy
    WHERE intName='Radiohead'
    GROUP BY nick, avatar
    ORDER BY SUM(nTimes) DESC
    I just want the first 10 entries returned and not the complete top. Is it possible to do this in SQL?


    Thank you for your attention.
    Ah! Não ser eu toda a gente e toda a parte!

  2. #2
    Join Date
    Dec 2003
    Posts
    50
    By the way... Is it possible to get a fixed number of random entries from a collumn obtained with a SELECT... ?
    Ah! Não ser eu toda a gente e toda a parte!

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is it possible to get a fixed number of random entries
    >from a collumn obtained with a SELECT
    Yes, use a SAMPLE clause.
    >I just want the first 10 entries returned and not the complete top.
    Define "first 10".
    Based upon what criteria?
    use WHERE ROWNUM < 11

  4. #4
    Join Date
    Dec 2003
    Posts
    50
    Thanks. Then, the query would be:

    Code:
    SELECT * FROM (
       SELECT nick, avatar, SUM(nTimes) 
       FROM RadioUser NATURAL JOIN listenedBy NATURAL JOIN releasedBy 
       WHERE intName='Radiohead'
       GROUP BY nick, avatar
       ORDER BY SUM(nTimes) DESC
    )
    WHERE ROWNUM < 11
    This way I get what I want. Regarding the second question I posted, maybe it's possible if we know the total number of rows in the table and then just get some random rows specified by a number inferior to the total number of rows.

    How can I know the total number of rows in a table?

    Is it possible to generate a random number between a known interval?
    Ah! Não ser eu toda a gente e toda a parte!

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I know the total number of rows in a table?
    SELECT COUNT(*) FROM <TABLE_NAME>
    >Is it possible to generate a random number between a known interval?
    DBMS_RANDOM

  6. #6
    Join Date
    Dec 2003
    Posts
    50
    OK! Thanks for the tips and sorry for these basic questions.
    Ah! Não ser eu toda a gente e toda a parte!

  7. #7
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You might want to try a top-n query...

    Code:
    SELECT nick, avatar, s_ntimes
    FROM
    (  SELECT nick, avatar, SUM(ntimes) s_ntimes
       FROM radioUser NATURAL JOIN releasedBy
       WHERE intName = 'RadioHead'
       GROUP BY nick, avatar
       ORDER BY SUM(ntimes) DESC
    )
    WHERE rownum < 10
    ORDER BY s_ntimes;
    I would imagine, though I've never tried it, that you could use the DBMS_RANDOM package in the "rownum < 10" part of the query.

    JoeB

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Getting a random number of rows...

    select * from table sample( n )

    where n = percent.

    There are restrictions on it's usage which mean it's not widely used. I wouldn't expect everyone to know about the sample() function, but for those who go on about RTFM, I would expect, or even *demand*, better. They can, after all, RTFM.

    Incidentally, sample() is neither OS version nor DB version dependant - it's certainly available on DB > 8, and OS version isn't an issue (just in case anyone asks).

    Hope that helps,
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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