SQL: Conditional Insert with order by

Question

Hi I have this item about insert if not exists here. One of the things I want to know about is if I want to get the latest items from CompResults by using order by ResultDate, to be inserted to Competitors table, how should I do it?

INSERT Competitors (cName)
SELECT DISTINCT Name
FROM CompResults cr
WHERE
   NOT EXISTS (SELECT * FROM Competitors c
              WHERE cr.Name = c.cName) ORDER BY cr.ResultsDate DESC

An error happens: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

1) ORDER BY makes no sense when doing INSERT. 2) The error message says it all. — jarlh, Nov 02 '16 at 07:36

score 1 · Answer 1 · answered Nov 02 '16 at 07:19

Hi you have to use order by fields in select statement I think You are using sql server
so You can use sub query

INSERT Competitors (cName)
select Name
from (
SELECT cr.Name,max(cr.ResultDate)
FROM CompResults cr
WHERE NOT EXISTS (SELECT * FROM Competitors c
WHERE cr.Name = c.cName) group BY cr.name) as t order by ResultDate

score 0 · Answer 2 · answered Nov 02 '16 at 06:55

Use Row_Number to get the latest record for each Item

Insert into Competitors(col1,col2..)
Select col1,col2,..
(
Select row_number()Over(partition by Name order by ResultDate desc) Rn, *
From CompResults  cr
NOT EXISTS (SELECT 1 FROM Competitors c
              WHERE cr.Name = c.cName)

) a
Where Rn = 1

score 0 · Answer 3 · answered Nov 02 '16 at 07:19

Also you can as the below:

INSERT Competitors (cName)
SELECT
    A.Name
FROM
    CompResults A INNER JOIN 
    (
        SELECT
            CR.Name,
            MAX(CR.ResultsDate) MaxResultsDate
        FROM
            CompResults CR
     ) B ON A.Name = B.Name AND A.ResultsDate = B.MaxResultsDate
WHERE   
   NOT EXISTS (SELECT 1 FROM Competitors c
              WHERE c.cName = A.Name)

SQL: Conditional Insert with order by

3 Answers3