Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: TOP 10 Query For Each User

    I have a table that keeps a log of al user inquiries. The business wants to a report that shows the last ten inquiries each user has made:

    A sample result would look like:

    User, Date, InquiryID
    George, 10/1/2003, 1000
    George, 10/2/2003, 1001
    George, 10/3/2003, 1002
    George, 10/4/2003, 1003
    George, 10/5/2003, 1004
    George, 10/6/2003, 1005
    George, 10/7/2003, 1006
    George, 10/8/2003, 1007
    George, 10/9/2003, 1008
    George, 10/10/2003, 1009
    Harold, 11/1/2003, 1010
    Harold, 11/2/2003, 1011
    Harold, 11/3/2003, 1012
    Harold, 11/4/2003, 1013
    Harold, 11/5/2003, 1014
    Harold, 11/6/2003, 1015
    Harold, 11/7/2003, 1016
    Harold, 11/8/2003, 1017
    Harold, 11/9/2003, 1018
    Harold, 11/10/2003, 1019



    DDL:
    Code:
    CREATE TABLE UserInquiries (
    InqiuryID INT IDENTITY(1,1),
    UserName Varchar(10),
    InquiryDate datetime)
    
    
    INSERT INTO UserInquiries (UserName, InquiryDate)
    SELECT 'George', '10/1/2003'
    UNION SELECT 'George', '10/2/2003'
    UNION SELECT 'George', '10/3/2003'
    UNION SELECT 'George', '10/4/2003'
    UNION SELECT 'George', '10/5/2003'
    UNION SELECT 'George', '10/6/2003'
    UNION SELECT 'George', '10/7/2003'
    UNION SELECT 'George', '10/8/2003'
    UNION SELECT 'George', '10/9/2003'
    UNION SELECT 'George', '10/10/2003'
    UNION SELECT 'George', '10/11/2003'
    UNION SELECT 'George', '10/12/2003'
    UNION SELECT 'Harold', '11/1/2003'
    UNION SELECT 'Harold', '11/2/2003'
    UNION SELECT 'Harold', '11/3/2003'
    UNION SELECT 'Harold', '11/4/2003'
    UNION SELECT 'Harold', '11/5/2003'
    UNION SELECT 'Harold', '11/6/2003'
    UNION SELECT 'Harold', '11/7/2003'
    UNION SELECT 'Harold', '11/8/2003'
    UNION SELECT 'Harold', '11/9/2003'
    UNION SELECT 'Harold', '11/10/2003'
    UNION SELECT 'Harold', '11/11/2003'
    UNION SELECT 'Harold', '11/12/2003'
    I'm brain dead on other things right now. Any help would be gratefully accepted.

    Regards,

    hmscott

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    select * from UserInquiries u
    where u.InqiuryID in
    (select top 10 s.InqiuryID from #UserInquiries s where s.UserName=u.UserName order by s.InquiryDate desc)
    order by u.UserName,u.InquiryDate desc

Posting Permissions

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