Thread: select top 3

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    3
    Rep Power
    0

    select top 3


    I have a table that holds comment records. The columns are :
    user_id, comment, date

    How can I write a select statement that returns the 3 most recent comment records for each user, all in one recordset.

    thanks for any help.
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    SELECT TOP 3 *
    FROM table
    ORDER BY date DESC
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    3
    Rep Power
    0
    scorp,
    this returns the top 3 across all users. i need to get the top 3 for each user. i've done it with a cursor, but i'd like to find a more efficient way.
    cj
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select * from t as q
    where date in (
    SELECT TOP 3 date
    FROM t
    where t.user_id = q.user_id
    ORDER BY date DESC)
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    3
    Rep Power
    0
    Thanks swampBoogie, it worked.

IMN logo majestic logo threadwatch logo seochat tools logo