CODEWITHSUNDEEP
sqlteradata
user1723699
user1723699

Reputation: 1091

Efficiently find last date in a table - Teradata SQL

Say I have a rather large table in a Teradata database, "Sales" that has a daily record for every sale and I want to write a SQL statement that limits this to the latest date only. This will not always be the previous day, for example, if it was a Monday the latest date would be the previous Friday.

I know I can get the results by the following:

SELECT s.*
FROM Sales s
JOIN (
      SELECT MAX(SalesDate) as SalesDate 
      FROM Sales
) sd 
ON s.SalesDate=sd.SalesDt

I am not knowledgable on how it would process the subquery and since Sales is a large table would there be a more efficient way to do this given there is not another table I could use?

Upvotes: 0

Views: 3424

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Another (more flexible) way to get the top n utilizes OLAP-functions:

SELECT *
FROM Sales s
QUALIFY
  RANK() OVER (ORDER BY SalesDate DESC) = 1

This will return all rows with the max date. If you want only one of them switch to ROW_NUMBER.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269673

That is probably fine, if you have an index on salesdate.

If there is only one row, then I would recommend:

select top 1 s.*
from sales s
order by salesdate desc;

In particular, this should make use of an index on salesdate.

If there is more than one row, use top 1 with ties.

Upvotes: 1

Related Questions