Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Unanswered: select top needs to return 2 records only - Read

    I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.

    id, team, date, setnr, series
    1, 3, 1/1/2013, 1, 1102
    1, 3, 1/1/2013, 2, 1231
    1, 3, 1/1/2013, 3, 1023
    1, 3, 1/5/2013, 4, 1024
    1, 3, 1/5/2013, 5, 1123
    1, 3, 1/5/2013, 6, 1232
    2, 2, 1/1/2013, 1, 1032
    2, 2, 1/1/2013, 2, 1221
    2, 2, 1/1/2013, 3, 1023
    2, 2, 1/5/2013, 4, 1231
    2, 2, 1/5/2013, 5, 1112
    2, 2, 1/5/2013, 6, 1231

    I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query

    select sum(series), date from table group by date order by sum(series) desc

    This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.

    Thanks for any help you can get....

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with CTE_R as
    (
        select 
            *, 
            ROW_NUMBER() OVER(PARTITION BY id, date ORDER BY setnr) as RowNum
        from MyTable
    )
    
    select * from CTE_R
    where RowNum <= 2
    Hope this help.

Posting Permissions

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