SQL Selecting record with highest ID

Question

I have a issue with some SQL that I can't wrap my head around a solution.

Right now the query I am running basically is:

SELECT Q.ID, Q.STATUS, C.LASTNAME, C.FIRSTNAME, 
       C.POSTAL, C.PHONE 
FROM QUEUE Q 
LEFT OUTER JOIN CUSTOMER C ON Q.ID = C.APPID 
WHERE C.LASTNAME LIKE 'SMITH%'

I have about 200 records from this query. My issue is the same person has multiple occurances.

Q.ID  Q.STATUS   C.LASTNAME   C.FIRSTNAME   ETC...
 1        A         SMITH        JOHN
 2        A         SMITH        RYAN
 3        B         SMITH        BRIAN
 100      A         SMITH        RYAN
 200      A         SMITH        RYAN

What I need returned instead is

Q.ID   Q.STATUS   C.LASTNAME   C.FIRSTNAME   ETC...
 1        A          SMITH         JOHN
 3        B          SMITH         BRIAN
 200      A          SMITH         RYAN

Can anyone point me in the right direction please. I have tried

SELECT WHATEVER FROM TABLE WHERE Q.ID IN (SELECT MAX(ID) FROM TABLE WHERE BLAH BLAH)

which worked when searching for "RYAN SMITH" specifically. But I need to show all results for SMITH with the highest IDs.

Any help is appreciated.

Cheers

So, for a given first name and last name, you want the largest id? — , Sep 26 '11 at 19:40
Guess that would help a bit...Pervasive v9.5..and yes they can search by lastname or first name — Jose Quervo, Sep 26 '11 at 19:41

score 2 · Accepted Answer · edited Sep 26 '11 at 19:45

2

I guess you could do something like below

SELECT WHATEVER FROM TABLE 
WHERE Q.ID IN 
    (SELECT MAX(ID) FROM TABLE 
     WHERE BLAH...BLAH 
     GROUP BY C.FIRSTNAME, C.LASTNAME)

Hope this helps!!

edited Sep 26 '11 at 19:45

Marco

56,740
14
129
152

answered Sep 26 '11 at 19:41

Praveen

1,449
16
25

score 0 · Answer 2 · answered Sep 26 '11 at 19:42

0

have you tried something like that? well it is not really a SQL statement, just showing the idea

select * from Table where id in (
         select max(q.id) from Table group by c.lastname, c,firstname
)

answered Sep 26 '11 at 19:42

Kent

189,393
32
233
301

Adriano Carneiro · Answer 3 · 2011-09-26T19:56:48.680

Assuming there is a CUSTOMER.ID, and I think I'm right, here it goes:

SELECT Q.ID, Q.STATUS, M.LASTNAME, M.FIRSTNAME, 
       M.POSTAL, M.PHONE 
FROM QUEUE Q 
LEFT OUTER JOIN (
  SELECT C2.ID CID, 
         MAX(C2.FIRSTNAME) FIRSTNAME, 
         MAX(C2.LASTNAME) LASTNAME, 
         MAX(C2.POSTAL) POSTAL,
         MAX(C2.PHONE) PHONE,
         MAX(Q2.ID) QID
  FROM QUEUE Q2 
  LEFT OUTER JOIN CUSTOMER C2 ON Q2.ID = C.APPID 
  WHERE C2.LASTNAME LIKE 'SMITH%'
  GROUP BY C2.ID
) M ON (M.QID = Q.ID)

score 0 · Answer 4 · answered Sep 26 '11 at 19:48

If I understand well, this should work:

SELECT Q.ID
     , Q.STATUS
     , C.LASTNAME
     , C.FIRSTNAME, 
     , C.POSTAL
     , C.PHONE
  FROM QUEUE Q 
  join CUSTOMER C ON Q.ID = C.APPID 
 WHERE C.LASTNAME like 'SMITH%'
   and not exists (SELECT *
                     FROM CUSTOMER innerCustomer
                    WHERE innerCustomer.LASTNAME like 'SMITH%'
                      and innerCustomer.APPID > C.APPID
                  )

Note: I have change "left join" by "inner join" because you are filtering per C.LASTNAME. So I think left join doesn't have many sense.

SQL Selecting record with highest ID

4 Answers4