SQL Select with Join and last record needed

Question

I've got the following tables:

devices table
SN     tested_device_id  test_setup_id  

129    6      103  
129    7      104   
130    8      106

test_setup table
test_setup_id data1  
103 111  
104 333  
106 555

I want to deliver SN, test_setup_id, data1 for the last instance of each

serial number as follows:  
129,104,333   
130,106,555

(Omitting the earlier copy of SN 129).

I have a query as follows which deliver both records for SN 129 and don't know how to condition it to only deliver the last of them:

SELECT sn, 
       test_setup_id, 
       data1 
FROM   test_setup 
       INNER JOIN devices 
         ON test_setup.test_setup_id = devices.test_setup_id

Any assistance would be welcomed. Thank you.

Conrad Frix · Answer 1 · 2011-04-15T19:59:05.193

1

Try this

SELECT d.sn, 
       d.test_setup_id, 
       ts.data1 
FROM   (SELECT d.sn, 
               MAX(d.test_setup_id) test_setup_id 
        FROM   devices d 
        GROUP  BY d.sn) maxsetupid 
       INNER JOIN devices d 
         ON maxsetupid.test_setup_id = d.test_setup_id 
       INNER JOIN test_setup ts 
         ON d.test_setup_id = ts.test_setup_id

edited Apr 15 '11 at 19:59

answered Apr 15 '11 at 19:53

Conrad Frix

51,984
12
96
155

score 0 · Answer 2 · answered Apr 15 '11 at 20:13

0

SELECT
  d.SN, d.test_setup_id, ts.data1
FROM devices d
  LEFT JOIN devices d2 ON d.SN = d2.SN and d.test_setup_id < d2.test_setup_id
  INNER JOIN test_setup ts ON d.test_setup_id = ts.test_setup_id
WHERE d2.SN IS NULL

answered Apr 15 '11 at 20:13

Andriy M

76,112
17
94
154

score -1 · Answer 3 · edited Apr 15 '11 at 19:47

-1

If the test_setup_id is always incrementing, you can do something like:

SELECT sn, 
       test_setup_id, 
       data1 
FROM   test_setup 
       INNER JOIN devices 
         ON test_setup.test_setup_id = devices.test_setup_id 
WHERE  test_setup_id = (SELECT MAX(test_setup_id) 
                        FROM   test_setup)

edited Apr 15 '11 at 19:47

Conrad Frix

51,984
12
96
155

answered Apr 15 '11 at 19:25

Eric Frick

847
1
7
18

-1: You're selecting a single max for the whole test_setup table, so the only row you'd return is `test_setup_id = 106` – Joe Stefanelli Apr 15 '11 at 19:34
the select it's used for the where. – Eric Frick Apr 15 '11 at 19:37
1

Your where clause, `where test_setup_id = (select max(test_setup_id) from test_setup)`, will only return 1 row, the case where `test_setup_id = 106` – Joe Stefanelli Apr 15 '11 at 19:40
Actually, I wanted the last row for each SN in the device table. Should get two result lines for my case. – Dave Apr 16 '11 at 14:03

SQL Select with Join and last record needed

3 Answers3