Implementing Hierarchy in SQL

Question

Suppose I have a table which has a "CDATE" representing the date when I retrieved the data, a "SECID" identifying the security I retrieved data for, a "SOURCE" designating where I got the data and the "VALUE" which I got from the source. My data might look as following:

CDATE   | SECID | SOURCE | VALUE
--------------------------------
1/1/2012    1       1        23
1/1/2012    1       5        45
1/1/2012    1       3        33
1/4/2012    2       5        55
1/5/2012    1       5        54
1/5/2012    1       3        99

Suppose I have a HIERARCHY table like the following ("SOURCE" with greatest HIERARCHY number takes precedence):

SOURCE  |  NAME | HIERARCHY
---------------------------
  1        ABC       10
  3        DEF        5
  5        GHI        2

Now let's suppose I want my results to be picked according to the hierarchy above. So applying the hierarch and selecting the source with the greatest HIERARCHY number I would like to end up with the following:

 CDATE   | SECID | SOURCE | VALUE
---------------------------------
1/1/2012    1       1        23
1/4/2012    2       5        55
1/5/2012    1       3        99

Also look at msdn.microsoft.com/en-us/library/bb677290.aspx for hierarchyid in sql 2008 — PMC
– PMC, Commented Jun 6, 2012 at 19:40

Chad · Accepted Answer · 2012-06-06 19:41:08Z

2

This joins on your hierarchy and selects the top-ranked source for each date and security.

SELECT CDATE, SECID, SOURCE, VALUE
FROM (
  SELECT t.CDATE, t.SECID, t.SOURCE, t.VALUE,
    ROW_NUMBER() OVER (PARTITION BY t.CDATE, t.SECID
                       ORDER BY h.HIERARCHY DESC) as nRow
  FROM table1 t
  INNER JOIN table2 h ON h.SOURCE = t.SOURCE
) A
WHERE nRow = 1

answered Jun 6, 2012 at 19:41

Chad

7,5772 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zhenny · Accepted Answer · 2012-06-06 20:04:21Z

1

You can get the results you want with the below. It combines your data with your hierarchies and ranks them according to the highest hierarchy. This will only return one result arbitrarily though if you have a source repeated for the same date.

;with rankMyData as (
    select
        d.CDATE
    ,   d.SECID
    ,   d.SOURCE
    ,   d.VALUE
    ,   row_number() over(partition by d.CDate, d.SECID order by h.HIERARCHY desc) as ranking
    from DATA d
      inner join HIERARCHY h
        on h.source = d.source
)
SELECT
    CDATE
,   SECID
,   SOURCE
,   VALUE
FROM rankMyData
where ranking = 1

edited Jun 6, 2012 at 20:04

answered Jun 6, 2012 at 19:42

Zhenny

8316 silver badges10 bronze badges

2 Comments

Denis Over a year ago

probably should partition by CDATE and SECID

Zhenny Over a year ago

Yeah, that makes sense. Was a little ambiguous from the original question.

Collectives™ on Stack Overflow

Implementing Hierarchy in SQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related