Limiting Results on Join Query

Question

Say I have the following tables:

|RefNumber| Charge| IssueDate|
------------------------------
|    00001|   40.0|2009-01-01|
|    00002|   40.0|2009-06-21|

|ID|RefNumber|Forename|   Surname|
---------------------------------
  1|    00001|     Joe|     Blogs|  
  2|    00001|   David|     Jones|
  3|    00002|    John|     Smith|
  4|    00002|    Paul|     Walsh|

I would like to select refnumber, charge and issuedate from the first table then join on refnumber to the second table to retrieve forename and surname but only get the row with the highest id.

So results would look like:

|RefNumber| Charge| IssueDate|ID|Forename|   Surname|
-----------------------------------------------------
|    00001|   40.0|2009-01-01| 2|   David|     Jones|
|    00002|   40.0|2009-06-21| 4|    Paul|     Walsh|

I am unsure who to limit results on the join to only return the record with the highest ID from the second table.

Answer 1

I'd probably join against a subquery that returns only record from the second table with the highest id.

select a.RefNumber, a.Charge, a.IssueDate, b.ID, b.Forename, b.Surname
from References a inner join
    (select ID, RefNumber, ForeName, Surname from Names n1
        where n1.ID = (select top 1 n2.ID from Names n2 where n1.RefNumber = n2.RefNumber) ) b
    on a.RefNumber = b.RefNumber

Answer 2

Actually your subquery would need to select the highest ID for EACH refnumber, so that would look more like this:

select 
        a.RefNumber, a.Charge, a.IssueDate, b.BiggestID, b.Forename, b.Surname
from References a 
inner join
    (select
           RefNumber,
           max(ID) as BiggestID
     from Names
     group by
           RefNumber) b
    on a.RefNumber = b.RefNumber

Hope that helps. -Tom

Answer 3

select nt.RefNumber, ct.Charge, ct.IssueDate, nt.ID, nt.Forename, nt.Surname
from NameTable nt
join ChargeTable ct on (ct.RefNumber = nt.RefNumber)
where nt.ID = (select MAX(nt2.id) 
               from NameTable nt2 
               where nt2.RefNumber = nt.RefNumber)
order by nt.ID

Answer 4

The most flexible way to write this, which doesn't require a correlated subquery, is to use ROW_NUMBER (SQL Server 2005+ only):

;WITH Names_CTE AS
(
    SELECT
        ID, RefNumber, Forename, Surname,
        ROW_NUMBER() OVER (PARTITION BY RefNumber ORDER BY ID) AS RowNum
    FROM Names
)
SELECT o.RefNumber, o.Charge, o.IssueDate, n.Forename, n.Surname
FROM Orders o
[INNER|LEFT] JOIN Names_CTE n
    ON n.RefNumber = o.RefNumber
WHERE n.RowNum = 1

Note that ROW_NUMBER isn't always the most efficient if you can use MIN/MAX instead, just the easiest to write.

If you're running SQL 2000, or this isn't efficient enough, you can try a MIN or MAX query:

SELECT o.RefNumber, o.Charge, o.IssueDate, n.Forename, n.Surname
FROM Orders o
[INNER|LEFT] JOIN
(
    SELECT RefNumber, MIN(ID) AS MinID
    FROM Names
    GROUP BY RefNumber
) m
    ON m.RefNumber = o.RefNumber
[INNER|LEFT] JOIN Names n
    ON n.ID = m.MinID

Sometimes this is actually faster, it depends a lot on the indexing strategy used.

(Edit - this gets rows with the lowest ID, which in most cases will be faster than getting the highest ID. If you need the highest, change the first query to ORDER BY ID DESC and the second query to use MAX instead of MIN).