Joe Christou Joe Christou - 6 months ago 9
SQL Question

SQL query to retrieve last record from a linked table

I wrote a query to compare 2 columns in different tables (

TRELAY
VS
TUSERDEF8
). The query works great, except that it retrieves the top record in the
TUSERDEF8
table which has a many to one relationship to the
TRELAY
table.

The tables are linked by
TRELAY.ID = TUSERDEF8.N01
. I would like to retrieve the latest record from
TUSERDEF8
and compare that record with the
TRELAY
record. I plan to use the max value of the index column (
TUSERDEF8.ID
) to determine the latest record.

I am using SQL Server.

My code is below, but I'm not sure how to change the query to retrieve the last
TUSERDEF8
record. Any help is appreciated.

SELECT
TRELAY.ID, TRELAY.S15,
TUSERDEF8.S04, TUSERDEF8.N01, TUSERDEF8.S06
FROM
TRELAY
INNER JOIN
TUSERDEF8 ON TRELAY.ID = TUSERDEF8.N01
WHERE
LEFT(TRELAY.S15, 1) <> LEFT(TUSERDEF8.S04, 1)
AND NOT (TRELAY.S15 LIKE '%MEDIUM%' AND
TUSERDEF8.S04 LIKE '%N/A%' AND
TUSERDEF8.S06 LIKE '%EACMS%')

Answer

Using an ID column to determine which row is "last" is a bad idea

Using cryptic table names like "TUSERDEF8" (how is it different from TUSERDEF7) is a very bad idea, along with completely cryptic column names like "S04".

Using prefixes like "T" for table is a bad idea - it should already be clear that it's a table.

Now that all of that is out of the way:

SELECT 
    R.ID,
    R.S15,
    U.S04,
    U.N01,
    U.S06
FROM 
    TRELAY R
INNER JOIN TUSERDEF8 U ON U.N01 = R.ID
LEFT OUTER JOIN TUSERDEF8 U2 ON
    U2.N01 = R.ID AND
    U2.ID > U.ID
WHERE
    U2.ID IS NULL AND    -- This will only happen if the LEFT OUTER JOIN above found no match, meaning that the row in U has the highest ID value of all matches
    LEFT(R.S15, 1) <> LEFT(U.S04, 1) AND
    NOT (
        R.S15 LIKE '%MEDIUM%' AND
        U.S04 LIKE '%N/A%' AND
        U.S06 LIKE '%EACMS%'
    )
Comments