CODEWITHSUNDEEP
sql-servermaxdate
BBAA
BBAA

Reputation: 55

SQL Server last date

Is there an option for getting the row with the highest date without joining the same table and use max(date) ?? Is Top1 order by desc a valid option ?

I use SQL Server 2000. And performance is important.

edit:

Table1:

columns: part - partdesc

Table 2:

columns: part - cost - date


select a.part,partdesc,b.cost
left join( select cost,part 
           right join(select max(date),part from table2 group by part) maxdate ON maxdate.date = bb.date  
           from table2 bb ) b on b.part = a.part
from table1

I don't know if the code above works but that is the query I dislike. And seems to me inefficient.

Upvotes: 2

Views: 3568

Answers (2)

JNK
JNK

Reputation: 65157

Here's a somewhat simplified query based on your edit.

SELECT 
    a.part,
    a.partdesc,
    sub.cost
FROM
    Table1 A
INNER JOIN
    (SELECT 
        B.part,
        cost
     FROM
        Table2 B
     INNER JOIN
        (SELECT
            part,
            MAX(Date) as MaxDate
         FROM 
            Table2
         GROUP BY
            part) BB
        ON bb.part = b.part
        AND bb.maxdate = b.date) Sub
    ON sub.part = a.part

The sub-sub query will hopefully run a little bit quicker than your current version since it'll run once for the entire query, not once per part value.

Upvotes: 1

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

SELECT TOP 1 columnlist
FROM table
ORDER BY datecol DESC

is certainly a valid option, assuming that your datacols are precise enough that you get the results needed (in other words, if it's one row per day, and your date reflects that, then sure. If it's several rows per minute, you may not be precise enough).

Performance will depend on your indexing strategy and hardware.

Upvotes: 1

Related Questions