SQL Selecting record with highest ID

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 i开发者_运维知识库s:

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

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!!

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
)

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.

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)