SQL query to retrieve last record from a linked table

Question

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%')

Sturgus · Answer 1 · 2016-05-20T16:18:00.410

0

I believe that your expected output is still a little ambiguous.

It sounds to me like you want only the record from the output where TUSERDEF8.ID is at its max. If that's correct, then try this:

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%')
  AND TUSERDEF8.ID IN (SELECT MAX(TUSERDEF8.ID) FROM TUSERDEF8)

EDIT: After reviewing your recent comments, it would seem something like this would be more suitable:

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

edited May 20 '16 at 16:18

answered May 20 '16 at 13:50

Sturgus

666
4
18

Thank you. I think this will work for what I need, but what do you mean by the output is ambiguous? – Joe Christou May 20 '16 at 15:26
@JoeChristou I mean that I'm not sure if you're asking for a 1-row output or something else (e.g., see the confusion in @Dave 's answer's comments). The query I've provided will give you 1 row as your output where `TUSERDEF8.ID` is at its current maximum (i.e., if that ID doesn't have the appropriate relation built in `TRELAY` yet, then you'll get a 0-row answer). This assumes an integer-based increasing `TUSERDEF8.ID`. If you need 1 row every time you run the query, then you should go with @Dave's answer, as it will give you the latest `TUSERDEF8.ID` that has all of the relations already. – Sturgus May 20 '16 at 15:36
Oh, my apologies. I want to cycle through all of the TRELAY records and do a comparison with the associated latest TUSERDEF8 record. To give a little background, we are doing a pre-assessment on each device to determine impact level, Low, Medium or No. then doing the official assessment in a different database. Those results are sent back to TUSERDEF8 and I want to do a comparison on the latest assessment (TUSERDEF8) to verify that the impact levels match. Hope that explains things. – Joe Christou May 20 '16 at 15:49
To add to that, after re-reading the responses. I want the Max(TUSERDEF8.ID) from only the TUSERDEF8 records linked with each TRELAY.ID. God, I hope that makes sense. – Joe Christou May 20 '16 at 15:52
@JoeChristou So you want a multi-record output where each `TRELAY` record is matched only with its (maximum) `TUSERDEF8.ID` record? I'll have to write up a different answer if that's the case. – Sturgus May 20 '16 at 16:03
Yes, that's exactly what I want. Tom's answer below appears to work for me. I really appreciate the help. I struggled with this for a while. – Joe Christou May 20 '16 at 16:19
@JoeChristou Tom's answer is fun (I've never used an `outer join U2.ID > U.ID` - like logic before). I'm curious if it would execute faster than my answer or not. Either way, you should mark one as 'correct'. – Sturgus May 20 '16 at 16:25
Thank you. I did mark Tom's answer correct since I was able to follow his code, but I really appreciate your help as well. I will definitely look over your code to make sure I understand it as well. Thanks again. – Joe Christou May 20 '16 at 16:34

score 0 · Answer 2 · answered May 20 '16 at 13:53

0

Making the assumption that your IDs are int(s) then the below might work?

SELECT TOP 1 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%'
    )
ORDER BY TUSERDEF8.ID DESC

HTH

Dave

answered May 20 '16 at 13:53

Dave

740
1
6
17

This is only going to give one row, not the top row for each `TRELAY` row. – Tom H May 20 '16 at 13:56
1

OK...didn't get the need for each row...think Joe C's CTE is probably the way to go... – Dave May 20 '16 at 14:01
I'm not sure how your query returned a "Low" in the TRELAY.S15 field. – Joe Christou May 20 '16 at 15:28
@Joe Christou The question stated "...I would like to retrieve the latest record from TUSERDEF8 ..." and thus the "ORDER BY TUSERDEF8.ID DESC"..max id would be listed first that matched the TRELAY record. I did fully realize the requirement to find each TRELAY record that has a matching maximum TUSERDEF8 record...apologies... – Dave May 23 '16 at 02:13

score 0 · Answer 3 · answered May 20 '16 at 13:56

You could do this:

With cteLastRecord As
(
Select  S04, N01, S06, 
        Row_Number() Over (Partition By N01, Order By ID Desc) SortOrder 
    From TUSERDEF8
)
SELECT 
    TRELAY.ID, TRELAY.S15, 
    TUSERDEF8.S04, TUSERDEF8.N01, TUSERDEF8.S06
FROM 
    TRELAY
INNER JOIN 
    (Select S04, N01, S06 From cteLastRecord Where SortOrder = 1) 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%')

score -1 · Accepted Answer · answered May 20 '16 at 13:55

-1

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%'
    )

answered May 20 '16 at 13:55

Tom H

46,766
14
87
128

Uncredited duplicate of the logic in this answer: http://stackoverflow.com/a/2111420/1507566 – Tab Alleman May 20 '16 at 14:10
It's "uncredited" because I just came up with the query myself. I don't scour the internet every time I type a query just to see if someone else might have typed it before. Do you give credit every time you write out a `WHILE` loop because someone once used one before you? – Tom H May 20 '16 at 14:29
Only commenting to give the reason for my downvote, not meant to be a personal criticism, and I certainly didn't mean to imply that you were deliberately plagiarising. I try not to downvote without a comment. Thank you for voting to close. – Tab Alleman May 20 '16 at 14:32
BTW, here's my use of the same method a year and a half before Bill Karwin posted it: http://stackoverflow.com/questions/227037/can-i-get-better-performance-using-a-join-or-using-exists/227363#227363. You should go down-vote him. – Tom H May 20 '16 at 14:33
Sure about that link? The answer I see seems to be about performance of JOINs vs EXISTS, which isn't the same thing at all. But if you did post this logic earlier than the answer I cited, I'd be happy to favorite it for future flagging. If you knew you'd already posted this logic, why not flag the dupe rather than answer? – Tab Alleman May 20 '16 at 14:41
Tom H., I didn't create the database. The tables, structure, etc. are from a COTS product. I do thank you for the help with the query. – Joe Christou May 20 '16 at 15:33

SQL query to retrieve last record from a linked table

4 Answers4