SQL Server last date

Question

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.

Why can't you use `MAX` date? A `TOP 1 ORDER BY DATE DESC` will be essentially the same operation but may be less efficient since it might order the whole table instead of just finding the highest value. — JNK, Mar 05 '12 at 14:01
@JNK with filters the table will have max 10 rows. Normal 2 rows. And from those rows I need the last date. — BBAA, Mar 05 '12 at 14:24
If you only have 10 rows then why is performance even a consideration? — JNK, Mar 05 '12 at 14:28
So you need this to run for a lot of individual parts? Can you show your table structure? — JNK, Mar 05 '12 at 14:31

score 1 · Answer 1 · answered Mar 05 '12 at 14:01

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.

score 1 · Accepted Answer · answered Mar 05 '12 at 15:00

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.

K, I will go with this. Thanks for your help. – BBAA Mar 05 '12 at 15:09 — BBAA, Mar 05 '12 at 15:09

SQL Server last date

2 Answers2