Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Nth row of Order By results

Results 1 to 2 of 2
Share This Thread →
  1. #1
    Geno45 is offline Contributing User
    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
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    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

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates