Help with SQL query.

innale · #1 (**permalink**) 02-17-04, 10:01

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.

joebednarz · #2 (**permalink**) 02-18-04, 14:06

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

Internet Services

Web Development

Digital Marketing

Consumer Tech