Click here to Skip to main content
14,775,288 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have a table (TABLE1)
Id1 Id2 dateTime
1    1  2020-08-06 10:45:38.017
2    1  2020-08-06 11:45:38.017
3    2  2020-08-06 10:45:38.017
4    3  2020-08-06 11:45:38.017
5    3  2020-08-06 12:45:38.017


i want to insert the bold ones into a new table(TABLE2) with one select statement
so i'm inserting the latest record for each repeated Id2

The new table has the same fields as well

What I have tried:

insert into TABLE2 select top 1  table1.Id1,table1.Id2, Table1.dateTime from table1 
where table1.Id2 = table2.Id2 order by DateTime desc


currently it's inserting one record
Posted
Updated 6-Aug-20 5:44am

1 solution

TOP 1 instructs SQL Server that you only want to return one record, so your query is doing exactly what you've asked it to do.

Based on your description, you want something like:
WITH cte As
(
    SELECT
        Id1,
        Id2,
        [dateTime],
        ROW_NUMBER() OVER (PARTITION BY Id2 ORDER BY [dateTime] DESC) As RN
    FROM
        table1
)
INSERT INTO table2
(
    Id1,
    Id2,
    [dateTime]
)
SELECT
    Id1,
    Id2,
    [dateTime]
FROM
    cte
WHERE
    RN = 1
;
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
v2
Comments
Member 14800672 6-Aug-20 12:53pm
   
Thanks aloot

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900