Btukfyl

This is probably very simple but I'm stupid and stuck and failed to find a thread that quite matched my problem...

I need to do an insert from a table, say tblGameRecords, that looks something like this:

tblGameRecords(ID:match_no, soccer_team_id, stadium, fake_injuries, hair_wax, date)

...into another table, tblTeamRecords, that needs to look like this:

tblTeamRecords(ID:soccer_team_id, stadium, fake_injuries, hair_wax, date)

Now, my problem is that in tblGameRecords:

1. There are natural multiple occurrences of the same soccer_team_id's.

2. There are natural multiple occurrences of the same date.

3. There are sometimes multiple occurrences of the same soccer_team_id on the same date (sigh...)

I want to insert into tblTeamRecord one row per soccer_team_id. I want the earliest record of that team from tblGameRecords.

If the team makes its entrée in tblGameRecords as a duplicate, several times on the same date, I'm fine with any one row of those, because the other columns need to be filled with the respective values from that row, regardless of the actual values which may or may not differ from the other duplicates.

And I'm obviously having trouble formulating a query that lets me narrow down these multiples to just one. This is part of a stored procedure btw.

* EDIT again: Deleted the redundant additional info *

You can use ROW_NUMBER to generate row numbers per date for each value of soccer_team_id (to partition the numbering by it) and then insert only those rows, where row number equals one:

;with cte as (

select soccer_team_id, stadium, fake_injuries, hair_wax, date, row_number() over(partition by soccer_team_id order by date) as row_no

from tblGameRecords

)

insert into tblTeamRecords(soccer_team_id, stadium, fake_injuries, hair_wax, date)

select soccer_team_id, stadium, fake_injuries, hair_wax, date

from cte

where row_no = 1