CODEWITHSUNDEEP
sql-serversql-server-2012
CodeNinja
CodeNinja

Reputation: 3278

Insert into select with order by for special case

I want to insert into my temp table the latest date that is less than my input date (2016-02-23). According to me the order by is necessary in the select statement.

The order by in the below query is causing an error. How can I change this query so that I can insert the latest date less than the input date?

Any ideas ?

INSERT INTO #tempEffDate(EffDate) 
    (SELECT TOP (1) EffDate 
     FROM ProductTable 
     WHERE EffDate < '2016-02-23' 
     ORDER BY effdate)

Upvotes: 0

Views: 80

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

Don't make it a subquery. I am guessing the error is that you can't have an order by in a subquery...of course posting that would be helpful.

INSERT INTO #tempEffDate(EffDate) 
SELECT TOP (1) EffDate 
FROM ProductTable 
WHERE EffDate < '2016-02-23' 
ORDER BY effdate

Upvotes: 2

Related Questions