How to write SQL query to extract log data

Question

I have two tables. One is a master table containing master data.

One column contains unique IDs. A second is a log table.

For one unique ID (master table ID) nearly 3 to 4 records are in the log table.

I want to fetch master data and its log table data (top record for unique id in descending order).

Eg.

Master table

id      guid       desc      depid 
----------------------------- 
1       fg1          hii           1
2       h6           hoo          2

Log table

id      guid   fromqueue     toqueue   status
-----------------------------------------------
1       fg1          1                        2                1
2       fg1          2                        3                2
3       h6           1                        4                2
4       h6           4                        5                3

For example, here I want to fetch data like below

id     guid       desc      depid    status  
-----------------------------------------------
1      fg1          hii              1           2   
2      h6           hoo              2           3

What is the query for getting this result?

possible duplicate of [select top 10 records for each category](http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category) — Clockwork-Muse, Jun 15 '14 at 08:21

score 2 · Accepted Answer · answered Jun 14 '14 at 11:02

2

Here is one method:

select m.*, l.status
from master m join
     log l
     on m.guid = l.guid
where not exists (select 1
                  from log l2
                  where l2.guid = l.guid and
                        l2.id > l.id
                 );

For performance, you want an index on log(guid, id). This transforms the statement to: "Get me all records from the log table that have the same guid and no higher id." That is equivalent to getting the last record, assuming the id column is an identity column.

answered Jun 14 '14 at 11:02

Gordon Linoff

1,242,037
58
646
786

1

@nixen09 . . . That surprises me. The code looks like it should be returning the status only from the log record with the highest id for each `guid`. Can you set up a SQL Fiddle? – Gordon Linoff Jun 14 '14 at 13:16
@ Gordon Sorry for the wrong statement..its working perfectly..thanks.. – nichu09 Jun 16 '14 at 04:47

score 0 · Answer 2 · answered Jun 14 '14 at 11:01

0

Try something like this:

SELECT   master.id AS id, master.guid AS guid, desc, depid, MAX(status) AS status
FROM     master
JOIN     log ON master.id = log.id
GROUP BY master.id, master.guid, desc

answered Jun 14 '14 at 11:01

Mureinik

297,002
52
306
350

hi, its not working . I tried . Its giving different status id. Not according to my above requirements . – nichu09 Jun 14 '14 at 13:12

How to write SQL query to extract log data

2 Answers2