1

Using SQL Server 2012

I have seen a few threads about this topic but I can't find one that involves multiple joins in the query. I can't create a VIEW on this database so the joins are needed.

The Query

SELECT 

      p.Price
      ,s.Type
      ,s.Symbol
      , MAX(d.Date) Maxed
  FROM AdventDW.dbo.FactPrices p
  INNER JOIN dbo.DimSecurityMaster s
  ON s.SecurityID = p.SecurityID
  INNER JOIN dbo.DimDateTime d
   ON
  p.DateTimeKey = d.DateTimeKey
  GROUP BY p.Price ,
           s.Type ,
           s.Symbol
ORDER BY s.Symbol

The query works but does not produce distinct results. I am using Order by to validate the results, but it is not required once I get it working. I The result set looks like this.

Price   Type    Symbol  Maxed
10.57   bfus    *bbkd           3/31/1989
10.77   bfus    *bbkd           2/28/1990
100.74049   cbus    001397AA6       8/2/2005
100.8161    cbus    001397AA6       7/21/2005

The result set I want is

Price   Type    Symbol  Maxed
10.77   bfus    *bbkd           2/28/1990
100.74049   cbus    001397AA6       8/2/2005

Here were a few other StackOverflow threads I tried but couldn't get t work with my specific query

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

SQL Selecting distinct rows from multiple columns based on max value in one column

Community
  • 1
  • 1
Jay C
  • 842
  • 6
  • 17
  • 37

3 Answers3

3

If you want data for the maximum date, use row_number() rather than group by:

SELECT ts.*
FROM (SELECT p.Price, s.Type, s.Symbol, d.Date,
             ROW_NUMBER() OVER (PARTITION BY s.Type, s.Symbol
                                ORDER BY d.Date DESC
                               ) as seqnum
      FROM AdventDW.dbo.FactPrices p INNER JOIN
           dbo.DimSecurityMaster s
           ON s.SecurityID = p.SecurityID INNER JOIN
           dbo.DimDateTime d
           ON p.DateTimeKey = d.DateTimeKey
     ) ts
WHERE seqnum = 1
ORDER BY s.Symbol;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This query achieves the desired result. Needed one small edit where I changed Order BY s.symbol to ts.symbol. Thank you. – Jay C Sep 27 '15 at 16:22
1

You should use a derived table since you really only want to group the DateTimeKey table to get the MAX date.

SELECT p.Price ,
       s.Type ,
       s.Symbol ,
       tmp.MaxDate
FROM AdventDW.dbo.FactPrices p
INNER JOIN dbo.DimSecurityMaster s ON s.SecurityID = p.SecurityID
INNER JOIN
  ( SELECT MAX(d.Date) AS MaxDate ,
           d.DateTimeKey
   FROM dbo.DimDateTime d
   GROUP BY d.DateTimeKey ) tmp ON p.DateTimeKey = tmp.DateTimeKey
ORDER BY s.Symbol;
Stenerson
  • 952
  • 8
  • 17
1
    /* 
    this is your initial select which is fine because this is base from your original criteria, 
I cannot ignore this so i'll keep this in-tact. Instead from here i'll create a temp
    */

  SELECT 
      p.Price
      , s.Type
      , s.Symbol
      , MAX(d.Date) Maxed
  INTO #tmpT
  FROM AdventDW.dbo.FactPrices p
  INNER JOIN dbo.DimSecurityMaster s
      ON s.SecurityID = p.SecurityID
  INNER JOIN dbo.DimDateTime d
      ON p.DateTimeKey = d.DateTimeKey
  GROUP BY p.Price ,
       s.Type ,
       s.Symbol
  ORDER BY s.Symbol

SELECT innerTable.Price, innerTable.Symbol, innerTable.Type, innerTable.Maxed 
FROM (

   SELECT
      ROW_NUMBER () OVER (PARTITION BY t1.Symbol, t1.Type, t1.Maxed ORDER BY t1.Maxed DESC) as row 
      , *
   FROM #tmpT AS t1
) AS innerTable
WHERE row = 1

DROP TABLE #tmpT
ken lacoste
  • 894
  • 8
  • 22
  • Your query produces the following errors Msg 4104, Level 16, State 1, Line 18 The multi-part identifier "t1.Symbol" could not be bound. Msg 4104, Level 16, State 1, Line 18 The multi-part identifier "t1.Type" could not be bound. Msg 4104, Level 16, State 1, Line 18 The multi-part identifier "t1.Maxed" could not be bound. Msg 4104, Level 16, State 1, Line 18 The multi-part identifier "t1.Maxed" could not be bound. – Jay C Sep 27 '15 at 16:21
  • My apologies, please see edit, see FROM #tmpT AS t1, yeah I missed this. – ken lacoste Sep 27 '15 at 16:23