Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Unanswered: Help with SQL query.

    Hi, I have table Games(ClientID, WinDate, Score)
    and I need to get the set of 3 best scores for each ClientID per a day,
    where day is "05:00:00 - 04:59:59".

    I'm using MS SQL Server and I can use sp and I can use more then one queries and cursors, but I have no idea how to make this script.
    The main problem is to make grouping by day with such definition of the day.

    Thanks.

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I'm an Oracle guy, so this is how you could do this in Oracle, but maybe it will help you if there is a graceful translation to MS SQL.

    Code:
    SELECT score, windate
    FROM ( 
       SELECT score, windate
       FROM games 
          WHERE rowid<= 3 
          AND clientid = var
          AND WinDate BETWEEN 
             (TO_DATE( TO_CHAR( SYSDATE-1 ) || ' 17:00:00'), 'dd-MON-yyyy hh24:mi:ss' ) AND
             (TO_DATE( TO_CHAR( SYSDATE ) || ' 16:59:59'), 'dd-MON-yyyy hh24:mi:ss' )
       ORDER BY score
    )
    rowid is the order in which the results are returned, SYSDATE is the current system date.

    Hope this helps or at least helps you think of another solution.

    JoeB

Posting Permissions

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