CODEWITHSUNDEEP
sqlsql-serversubquery
jadenohiokan
jadenohiokan

Reputation: 9

SQL Most Efficient Way to Select another query

I have the following table setup:

TableA
Name    Loc
Bob     US
Bill    CA


TableB
Name    Phone    Updated
Bob     123      121011
Bob     321      121150

I have table A and B where I want to run a query that joins the two and gets the most recently updated (highest value in Updated (whichever number is larger)) phone number from table B and only returns one line. I currently am using this query:

select
      A.Name,
      A.Loc,
      B.Phone
   from
      TableA A
         INNER JOIN TableB B
            on A.Name = B.Name
   where
      A.Name = 'Bob'

I'm not sure how I can sort only B.Phone by column updated and return only the line containing in one query:

Bob, US, 321 (321 because it is the highest value updated)

Any help or insight would be much appreciated.

Upvotes: 0

Views: 210

Answers (3)

T N
T N

Reputation: 9959

Perhaps a CROSS APPLY (SELECT TOP 1...) approach would be simplest.

select
    A.Name,
    A.Loc,
    LATEST_B.Phone
from TableA A
cross apply (
    select top 1 B.*
    from TableB B
    where B.Name = A.Name
    order by B.Updated desc
) LATEST_B
where
      A.Name = 'Bob'

For best performance with big data, ensure that you have an index on TableB(Name,Updated).

Upvotes: 0

Paul Pearce
Paul Pearce

Reputation: 2530

You can use the Rank() function to generate the results you are looking for:

Select 
  Name, 
  Loc, 
  Phone 
From ( Select 
         A.Name, 
         A.Loc, 
         B.Phone, 
         Rank() Over (Partition By A.Name Order By B.Updated Desc) as Rank
       From TableA A Inner Join TableB B On A.Name = B.Name
       Where A.Name = 'Bob') As DataTable
Where Rank = 1

Upvotes: 0

DRapp
DRapp

Reputation: 48139

You have a couple things going on. I edited the post and manually put the text of the sample data and the sql query (you did not have commas after each column per the image).

Also, your data shows an updated column, but unclear what it is... You mention the 123 record is the highest update value, but not sure on context. Does not look like a DATE field. If it IS, then you should always use proper data types such as a datetime field. It will be easier in the long run when you need to query things on date ranges vs some fixed text format that needs to be restructured to get what is intended.

Please EDIT your post and put the clarification in there and you can comment me back to review. Dont try to put long-winded/content in a comment. Edit and update your actual post.

REVISED QUERY.

Another clarification on a bad sample data. By using just a name, what if there were 100 "Bob" names in Canada. Use an auto-increment ID key to tie things together, not just a name. What if Bob changes the name to Robert, then you are all messed-up. For secondary tables from their respective primary, use the ID that ties back to the original.

To get what you want, you will need a sub-query to find what the most recent date IS for the phone lookup secondary table for the person in question.

select
      A.Name,
      A.Loc,
      B.Phone
   from
      TableA A
         INNER JOIN 
         ( select 
                  B1.Name,
                  max( B1.Updated ) LatestRecord
              from TableB B1
              where B1.Name = 'Bob' 
              group by B1.Name ) MostRecentPhone
            on A.Name = MostRecentPhone.Name
            JOIN TableB B
               on MostRecentPhone.Name = B.Name
               AND MostRecentPhone.LatestRecord = B.Updated
   where
      A.Name = 'Bob'

So, the first FROM and WHERE clause gets your "Bob" requirement.

The inner join to the sub-query gets the name and the most recent updated stamped record regardless of the phone PER name, but also filters only on "Bob", but again, issue if dealing with IDs vs name matches.

So now I have the subquery returning Bob with the Updated value = 121150

Now, can join again to the FINAL TableB instance based on the matched name, AND the updated value matching that of the subquery.

Upvotes: 1

Related Questions