CODEWITHSUNDEEP
c#sqloracle-database
Aht
Aht

Reputation: 593

Grabbing only one ( newest ) detail record from child table for multiple parent Id's in one query

I have problem with one query and to be honest i have no idea if this is even possible to write in good and readable way.

I have 2 table.

First is Event with 4 columns :

EVENT_ID | EVENT_TYPE | DETAIL_TYPE | DETAIL_ID

Second table is EVENT_DETAIL with more than 10 columns but we need only 3 of them:

DETAIL_ID | ..... | TIME | DESCRIPTION.

In current scenario we are storing Events in EVENT table. Each event can have multiple details with multiple types.

What i need to do is grab description DESCRIPTION for EVENT_ID that have latest EVENT_DETAIL.TIME

I created this SQL and maybe it is not good looking but at least it work: 

SELECT * FROM EVENT_DETAIL ED
WHERE ED.DESCRIPTION IN
(SELECT * FROM
( SELECT ED.DESCRIPTION
FROM EVENT_DETAIL ED
JOIN EVENT E ON ED.DETAIL_ID = E.DETAIL_ID AND 
E.EVENT_ID = 10 AND E.EVENT_TYPE  = 1 AND  E.DETAIL_TYPE = 1
WHERE ROWNUM = 1
ORDER BY ED.TIME DESC));

Problem is that in that SQL i can pass only one EVENT_ID ( i know that i can join it multiple times but I don't think that this will be good idea ) and i need to run this query for for example ten EVENT's (EVENT_TYPE and DETAIL_TYPE will be same for whole time ).

In current product i have solution in C# code. Fist I Grab all DETAILS_ID for Each EVENT and then calling another SQL to grab all details for this id and sorting it by use LINQ garbing latest. Technically this work but is very slow.

So my question is can I have one SQL statement to grab latest DESCRIPTION for each EVENT_ID that I pass ( EVENT_TYPE and DETAIL_TYPE will be same for each EVENT_ID) ?

So for data like :

EVENTS :

10    |    1    |    1    |    1
10    |    1    |    1    |    2
10    |    1    |    1    |    3
11    |    1    |    1    |    5
11    |    1    |    1    |    6
11    |    1    |    1    |    7

EVENT_DETAIL:

1     |    12-12-2013    |    TEST1    
2     |    12-13-2013    |    TEST2    
3     |    12-14-2013    |    TEST3      
5     |    12-13-2013    |    TEST4    
6     |    12-14-2013    |    TEST5   
7     |    12-15-2013    |    TEST6

And parameters EVENT_ID in (10,11) , EVENT_TYPE = 1 , DETAIL_TYPE = 1

I want to have results like :

3    |    TEST3
7    |    TEST6

Thanks.

Upvotes: 0

Views: 36

Answers (3)

jose_bacoy
jose_bacoy

Reputation: 12704

I would use a windowing function for this purpose. Hope this helps. Thanks.

select EVENT_ID, 
       DESCRIPTION
from (  
    select E.EVENT_ID, 
           ED.DESCRIPTION,
           row_number() over (partition by DETAIL_ID order by TIME desc) as rnum
    from EVENT E
    join EVENT_DETAIL ED 
    on (ED.DETAIL_ID = E.DETAIL_ID)) t
where t.rnum = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270693

If I understand the question correctly, you can use window function to get the most recent detail of each type for an event:

SELECT *
FROM (SELECT ED.*,
             ROW_NUMBER() OVER (PARTITION BY E.EVENT_ID, E.DETAIL_TYPE ORDER BY ED.TIME DESC) as seqnum
      FROM EVENT_DETAIL ED JOIN
           EVENT E
           ON ED.DETAIL_ID = E.DETAIL_ID
     ) ED
WHERE seqnum = 1;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133390

You could use a join with the max(ed.time) group by detail_id

select * from 
FROM EVENT_DETAIL ED 
inner join (
      select ED.DETAIL_ID,   max(ED.TIME) max_time
      FROM EVENT_DETAIL ED
      JOIN EVENT E ON ED.DETAIL_ID = E.DETAIL_ID 
      group by ED.DESCRIPTION 
) T.DETAIL_ID = ED.DETAIL_ID  AND ED.TIME = T.max_time

Upvotes: 1

Related Questions