Most Recent?

[cjkenworthy]

I have a 1:M relationship between two tables A and B. If I do a join over A.id = B.id it will obviously retrieve all records from table B which join over .id with table A.

How do I have so that it only retrieves one record from B (the most recent in B based on a datetime value in B)?

A: (record_id, firstname, surname ...)
B: (message_id, record_id, dateadded ...)

so I join over A.record_id = B.record_id, but only want to return one record from B where 'dateadded' is the most recent.

How can I do this?

 

[mrees]

Try this (It depends on there being a distinct set of dateadded's for each record_id)

SELECT
  *
FROM
  a JOIN
  (
  SELECT
    b.record_id,
    b.message_id,
    b.dateadded
  FROM
    (
    SELECT
      record_id,
      max(dateadded) dateadded
    FROM
      b
    GROUP BY
      record_id
    )mx JOIN
    b ON mx.record_id = b.record_id AND mx.dateadded = b.dateadded
  )maxb ON a.record_id = maxb.record_id

mrees

 

[cjkenworthy]

I was hoping to fit:

WHERE A.record_id IN (SELECT TOP 1 record_id FROM B ORDER BY DateAdded DESC)

somewhere in there for a quick and efficient way of retrieving the top (most recent) dateadded record.

Is there any way of doing this?

 

[mrdenny]

You will probally want to do a fetch to return that info. If you need sample code let me know, I can provide.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[r937]

unless i totally misunderstand the problem, this is a fairly straightforward situation, easily solved with a correlated subquery

select A.record_id
     , A.firstname
     , A.surname
     , B.message_id
     , B.dateadded
  from A     
inner
  join B
    on A.record_id 
     = B.record_id
 where B.dateadded 
     = ( select max(dateadded)
           from B
          where record_id 
              = B.record_id )

rudy

http://r937.com/

SQL Consulting

 

[r937]

uh oh, alias problem

select A.record_id
     , A.firstname
     , A.surname
  from A     
inner
  join B
    on A.record_id 
     = B.record_id
 where B.dateadded 
     = ( select max(dateadded)
           from B [b]as b2[/b]
          where [b]b2[/b].record_id 
              = B.record_id )

rudy

http://r937.com/

SQL Consulting