0

Is there a better way? I need attributes of the oldest SequenceID of oldest DateID for each ThingID. PK is DateID & SequenceID:

SELECT
          ThingID     = F1.ThingID
        , Attribute1  = F1.Attribute1
        , Attribute2  = F1.Attribute2
        , Attribute3  = F1.Attribute3
FROM dbo.Fact F1
INNER JOIN (
              SELECT  DateID      = F2.DateID
                    , SequenceID  = min(SequenceID)
                    , ThingID     = F2.ThingID
              FROM dbo.Fact F2
              INNER JOIN  (
                              SELECT    DateID   = min(DateID)
                                      , ThingID  = ThingID
                              FROM      dbo.Fact
                              GROUP BY  ThingID

                          ) AS F3 ON  F2.ThingID = F3.ThingID
                                  AND F2.DateID  = F3.DateID

              GROUP BY F2.DateID, F2.ThingID

            ) AS F2  ON   F1.ThingID    = F2.ThingID
                    AND   F1.SequenceID = F2.SequenceID
                    AND   F1.DateID     = F2.DateID

FACT TABLE

DateID       datetime  PK
SequenceID   INT       PK
ThingID      INT
AttributeID1 INT
AttributeID2 INT
AttributeID3 INT

edit: SQL 2012

  • I assume this is for SQL Server. Which version? – ypercubeᵀᴹ Dec 6 '16 at 23:51
3

You can use window functions for this:

WITH ct AS
  ( SELECT
        ThingID,
        Attribute1,
        Attribute2,
        Attribute3,
        Rn = ROW_NUMBER() OVER (PARTITION BY ThingID
                                ORDER BY DateID, SequenceID)
    FROM dbo.Fact
  ) 
SELECT
    ThingID,
    Attribute1,
    Attribute2,
    Attribute3
FROM ct
WHERE Rn = 1 ;

and an index on (ThingID, DateID, SequenceID).

| improve this answer | |
  • I think it should be ORDER BY ... DESC since the oldest is value is needed – a1ex07 Dec 6 '16 at 23:48
  • @a1ex07 they use MIN() – ypercubeᵀᴹ Dec 6 '16 at 23:49
  • oops , my fault. – a1ex07 Dec 6 '16 at 23:51
  • 1
    Thank you @ypercubeᵀᴹ ...off to do some window function exercises. – Alan Whited Dec 7 '16 at 14:44
1

Try turning F3 and F2 into temp tables (joys of using TSQL).

That is, F3 as the first temp table, then F2 joining F3, then F1 selecting the required data from F2.

Alternatively, you can do the same, but use CTEs. However, being MSSQL, temp tables would likely run faster. However, in certain situations Temp tables aren't allowed, whereas CTEs are. Just go with whichever is more efficient. Temp or CTE.

| improve this answer | |
  • Thank you @Nexmilitis, I'll compare the performance both ways. – Alan Whited Dec 7 '16 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.