#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2009
    Posts
    45
    Rep Power
    5

    Nth row of Order By results


    I have the following query that returns several records for multiple racers. I would like to query the 3rd endtime (if it exists) for each racer.

    select id, raceid, endtime, regresultsid from rfidlaps
    where raceid='150'
    order by regresultsid, cast(endtime as datetime)

    Here is a sample of data returned with this query... My goal is to return the Blue highlighted endtimes only

    Thank you in advance for any assistance.

    94051 150 18:21:13.95 29592
    94085 150 18:26:19.95 29592
    94086 150 18:31:33.72 29592
    94137 150 18:36:54.70 29592
    94171 150 18:42:18.70 29592
    94197 150 18:47:28.97 29592
    94226 150 18:52:51.98 29592
    94252 150 18:58:07.71 29592
    94262 150 19:03:11.98 29592
    94067 150 18:21:08.69 29607
    94121 150 18:26:20.69 29607
    94122 150 18:31:38.96 29607
    94164 150 18:36:56.70 29607
    94189 150 18:42:08.99 29607
    94219 150 18:47:33.98 29607
    94246 150 18:52:54.71 29607
    94259 150 18:58:18.98 29607
    94279 150 19:03:34.98 29607
    93850 150 17:38:16.89 29619
    93893 150 17:43:40.93 29619
    93922 150 17:49:08.93 29619
    93981 150 17:55:03.94 29619
    93982 150 18:00:44.68 29619
    94030 150 18:06:31.96 29619
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    something like
    Code:
    ;with t as
    (
    select ROW_NUMBER() over(order by raceid, cast(endtime as datetime)) as row,
     id, raceid, endtime, regresultsid 
    from rfidlaps
    where raceid='150' 
    )
    select * from t where row = 3
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Similar Threads

  1. Replies: 5
    Last Post: June 3rd, 2009, 12:53 PM
  2. ASP query results different from SQL manager results
    By mart0369 in forum ASP Development
    Replies: 3
    Last Post: July 24th, 2008, 11:49 AM
  3. Force the "ORDER BY" to be in ascending order??
    By fletchsod in forum Microsoft SQL Server
    Replies: 4
    Last Post: July 10th, 2007, 11:36 AM
  4. Search results displays all results?
    By jarv in forum ASP Development
    Replies: 17
    Last Post: April 25th, 2007, 11:02 AM
  5. Replies: 1
    Last Post: April 27th, 2004, 12:00 PM

IMN logo majestic logo threadwatch logo seochat tools logo