Menu
I've got two tables. Table "B" has a one to many relationship with Table "A", which means that there will be many records in table "B" for one record in table "A".
The records in table "B" are mainly differentiated by a date, I need to produce a resultset that includes the record in table "A" joined with only the latest record in table "B". For illustration purpose, here's a sample schema:
Table A ------- ID Table B ------- ID TableAID RowDate I'm having trouble formulating the query to give me the resultset I'm looking for any help would be greatly appreciated.
504
ORDER BY customer_id; In the above SQL queries: We use the ROW_NUMBER() function to number the rows in the orders Note that before numbering the rows, we group them by customer ID with PARTITION BY and sort them by date in descending order to get the most recent order in the top row.
To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.
In SQL Server, you can use these join clauses in the UPDATE statement to perform a cross-table update. In this syntax: First, specify the name of the table (t1) that you want to update in the UPDATE clause. Next, specify the new value for each column of the updated table.
SELECT * FROM tableA A OUTER APPLY (SELECT TOP 1 * FROM tableB B WHERE A.ID = B.TableAID ORDER BY B.RowDate DESC) as B 
select a.*, bm.MaxRowDate from ( select TableAID, max(RowDate) as MaxRowDate from TableB group by TableAID ) bm inner join TableA a on bm.TableAID = a.ID If you need more columns from TableB, do this:
select a.*, b.* --use explicit columns rather than * here from ( select TableAID, max(RowDate) as MaxRowDate from TableB group by TableAID ) bm inner join TableB b on bm.TableAID = b.TableAID and bm.MaxRowDate = b.RowDate inner join TableA a on bm.TableAID = a.ID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
