asked
SubQuery Table to Get Selective Display
I need to write a sql query to display an output as Table Result. I have two tables - Table1 and Table 2 Table1 contains ID, Name, Type, and Description and I need all the records whose Type starts with 0x i.e. Select * from Table1 where Type LIKE '0x%' Table2 contains CaseID, DocID, Type, and Date fields. Based on Type field in Table1, I need to display CaseID, DocID and Date. Display TOP 1 records from Table2 whose Date is latest and matched 0x type from Table1. Display should look like Result Table. Kindly assist. ![alt text][1] [1]: /storage/temp/2439-untitled.jpg
I would join the 2 tables together on [type] and then use a row_number() function to identify the latest for each type. Something like this (but I haven't properly tested as I don't want to type out all the data from your images.....) select
AllRows.ID, AllRows.Name, AllRows.Type, AllRows.Description, AllRows.CaseID, AllRows.DocID, AllRows.Date from ( select
Table1.ID, Table1.Name, Table1.Type, Table1.Description, Table2.CaseID, Table2.DocID, Table2.Date, row_number()over(partition by Table1.Type order by Table2.[Date] desc) as rn from Table1 join Table2 on Table2.[Type] = Table1.[Type] where Table1.Type like '0x%' ) AllRows where rn =1
18 People are following this question.
