CODEWITHSUNDEEP
inner-joinsql
MichaelMcCabe
MichaelMcCabe

Reputation: 523

Select statement to get a specific row from an inner join

The question I have is kind of simular to this one - MySQL select 1 row from inner join

However, it was never really answered in the way that I needed it. So I thought I would ask.

I have a table A (tableA) and this table has a load of states in tableB. New states keep being added all the time.

TableB has a column 'State' which has a foreign key to TableA.Id. This table B has a value and timestamp

I am trying to get ONE query which will bring back ALL values in TableA, with an inner join to bring the LATEST 'value' of those rows from tableB. The latest being the one with the latest time.

I know how to do an inner join where it needs to be, and I know how to order a table to bring back the latest 'time' row, however I dont know how to put these two together and create a single query.

I'm sure I will have to have a Select within a select, which is fine. However, what I am trying to avoid is to bring a DataTable back with all my results from TableA and do a query for each of those on tableB seperately.

Any ideas?

EDIT - I have made a mistake with the question, I only really noticed when trying to implement one of the answers.

I have a foreign key between TableA.id and TableB.proId

TableA - 'Id', 'Name' TableB - 'Id', 'proId', 'State', 'time'

I want to bring back all values of TableA with a join on B, to bring back the 'State' of the Max time

Upvotes: 0

Views: 4916

Answers (3)

Nitin Midha
Nitin Midha

Reputation: 2268

SELECT T.Id,
       T.Name,
       T.State
  FROM 
(
    SELECT A.ID,
           A.Name,
           B.State,
           ROW_NUMBER() OVER ( PARTITION BY A.ID ORDER BY B.Timestamp DESC) RowNumber
      FROM Table_A A
     INNER JOIN Table_B B on A.Id = B.ProdId
) T
WHERE T.RowNumber = 1

Upvotes: 1

msib
msib

Reputation: 93

Select Table_A.*
From Table_A 
LEFT JOIN 
(Select State, max(timestamp) 
From Table_B
Group BY State) b 
ON Table_A.Sate = b.State

Upvotes: 0

beerbajay
beerbajay

Reputation: 20270

Assuming there's no duplicate timestamps for a given state:

select table_a.*, foo.value as latest_value
from table_a left outer join (
    -- selects the value for the given state w/ the max timestamp
    select bar.*, table_b.value from (
        -- selects the max timestamp for a given state
        select state, max(timestamp) 
        from table_b 
        group by state
    ) bar
    left outer join table_b on 
    table_b.state = bar.state and
    table_b.timestamp = bar.timestamp
) foo 
on foo.state = table_a.id

Alternatively, you can use first_value, which is a little less verbose but more obscure:

select table_a.*, foo.value as latest_value
from table_a left outer join (
    select distinct state, 
    first_value(value) over (
        partition by state order by timestamp desc
    )
    from table_b
) foo
on foo.state = table_a.id

Upvotes: 0

Related Questions