CODEWITHSUNDEEP
sqlsql-serversubquerymultiple-columns
Lars Ljungberg
Lars Ljungberg

Reputation: 313

Subquery to return multiple columns from newest record?

I got a table structure like this:

Document

ID  | NAME  | DATA
----+-------+--------
1   | Doc1  | Data1
2   | Doc2  | Data2

History:

HID | DOC_ID    | HDATA1    | HDATA2    | HDATA3
----+-----------+-----------+-----------+---------
1   | 1         | A         | B         | C
2   | 2         | C         | D         | E
3   | 1         | A         | A         | B
4   | 1         | B         | B         | D
5   | 2         | E         | A         | C

The output I like to get is the fields NAME,DATA and HDATA1,HDATA2 and HDATA3 values from the newest record in HISTORY (highest HID) that matches the corresponding ID from the Documents table.

The examples I have found only returns one value from one column, but I need data from several columns. I can't figure how to set up this SQL.

Upvotes: 0

Views: 520

Answers (4)

jophab
jophab

Reputation: 5519

select * from Document d
inner join (
            select h1.* from  History h1
            inner join (
                         select max(hid) as hid from History group by doc_id 
                        ) h2
            on h1.hid = h2.hid
           ) t
on t.doc_id = d.doc_id

Upvotes: 0

varun madikunta
varun madikunta

Reputation: 18

The below query would return the newest record for each Document ID

   SELECT 
            ID, Name, DATA, HDATA1, HDATA2, HDATA3 
   FROM Document D CROSS APPLY (SELECT TOP 1 HDATA1, HDATA2, HDATA3 FROM History H WHERE D.ID=H.DOC_ID ORDER BY H.HID DESC) A

Query cost would be relatively low by using cross apply in this scenario.. It could show up the best results in query estimated execution plan.

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

There is a much elegant way to achieve this using window function row_number in the ordering clause in conjunction with TOP 1 with ties. No Subquery needed.

select top 1 with ties *
from document d
join history h
    d.id = h.doc_id
order by row_number() over (
            partition by d.id
            order by h.hid desc
            );

It returns one record per document with latest History ID.

Upvotes: 0

Benjamin
Benjamin

Reputation: 139

Following Example works for Oracle and might be adapted slightly for different SQL dialects:

SELECT * 
  FROM ( SELECT h.*,
                d.*,
                RANK() OVER (PARTITION BY h.DOC_ID ORDER BY h.HID DESC) AS RNK
           FROM History h
           JOIN Document d ON d.ID = h.DOC_ID )
 WHERE RNK = 1

Tested with following data:

INSERT INTO Document VALUES ( 1   , 'Doc1'  , 'Data1' );
INSERT INTO Document VALUES ( 2   , 'Doc2'  , 'Data2' );

INSERT INTO History VALUES ( 1   , 1         , 'A'         , 'B'         , 'C' );
INSERT INTO History VALUES ( 2   , 2         , 'C'         , 'D'         , 'E' );
INSERT INTO History VALUES ( 3   , 1         , 'A'         , 'A'         , 'B' );
INSERT INTO History VALUES ( 4   , 1         , 'B'         , 'B'         , 'D' );
INSERT INTO History VALUES ( 5   , 2         , 'E'         , 'A'         , 'C' );

Result:

HID     DOC_ID HDATA1     HDATA2     HDATA3             ID NAME       DATA              RNK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         4          1 B          B          D                   1 Doc1       Data1               1 
         5          2 E          A          C                   2 Doc2       Data2               1

Upvotes: 2

Related Questions