SQL Server SQL Query Last value of child table

kcrjzv8t  于 2023-02-28  发布在  其他
关注(0)|答案(2)|浏览(144)

I have two tables where primary key is uniqueidentifier and there are not dates inside of them. One table has info where is stored and has relationship to another table which is tracking status, each status is new row for history purpose. Now the problem is taking last row from second table and status.

Table 1
| Column | Type |
| ------------ | ------------ |
| Id | uniqueidentifier |
| BoxNum | nvarchar(25) |

Table 2

ColumnType
Iduniqueidentifier
statusint
Table1_FK_IDuniqueidentifier

Status has the following meaning:

0 for new
10 - approved
20 - rejected
30 - blocked

Each box can be rejected than approved or opposite. So my question is how I can get last inserted record in table2, something like this:

Result
| BoxNum | Status |
| ------------ | ------------ |
| 444444 | 10 |
| 444445 | 20 |
| 444412 | 30 |

s8vozzvw

s8vozzvw1#

If uniqueidentifier is sequential you can also use subquery like that;

SELECT 
   T1.BoxNum,
   (SELECT TOP 1 T2.Status 
      FROM [Table 2] T2 
         WHERE T2.Table1_FK_ID = T1.Id ORDER BY T2.Id DESC) AS Status
FROM [Table 1] T1

But if there is no related data in second table it could be error, then you need to check data existence.

wd2eg0qa

wd2eg0qa2#

I am guessing this is simplified example of your data. Basically, you need a column on which to order the status records in the second table.

If your uniqueidentifier is SEQUENTIAL the solution will be:

SELECT TOP 1 WITH TIES T1.[BoxName]
                      ,T2.[Status]
FROM [Table 1] T1
INNER JOIN [Table 2] T2
    ON T1.[Id] = T2.[Table1_FK_ID]
ORDER BY ROW_NUMBER() OVER (PARTITION BY T1.[Id] ORDER BY T2.[ID] DESC);

but if it is not you need to add record_id or date_add columns in order to know which record is the last one inserted.

相关问题