CODEWITHSUNDEEP
sqlsql-servert-sql
Nelson Gomes Matias
Nelson Gomes Matias

Reputation: 1997

Use DECLARE inside a VIEW in SQL Server

I need to do something like that:

CREATE VIEW MyView AS 
DECLARE @MAXDATE AS Date 
@MAXDATE = SELECT MAX(insertdate) FROM Mytable
SELECT * FROM Mytable WHERE insertdate= MAXDATE

Everyday new rows are inserted into my table, and I need to get the subset of the last insert. But I can't do it in this way as DECLARE is not allowed with views.

Anyone, anyidea?

thx!

Upvotes: 1

Views: 845

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You could also do this as:

CREATE VIEW MyView AS 
    SELECT TOP (1) WITH TIES t.*
    FROM Mytable t
    ORDER BY CAST(insertdate as date) DESC;

If insertdate is already declared as a date, then the cast() is not necessary:

CREATE VIEW MyView AS 
    SELECT TOP (1) WITH TIES t.*
    FROM Mytable t
    ORDER BY insertdate DESC;

For performance, I would recommend an index on mytable(insertdate).

Upvotes: 2

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

SELECT * FROM Mytable WHERE insertdate = (SELECT MAX(insertdate) FROM Mytable)

Upvotes: 2

Related Questions