question

souravagar avatar image
souravagar 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
sql-server-2012tsqlsql query
untitled.jpg (74.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

souravagar avatar image souravagar commented ·
Thank you Kev. This query worked for me.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.