Problems with SQL Inner join

Question

Hi all,

Having some problems while trying to optimize my SQL.

I got 2 tables like this:

Names
id, analyseid, name

Analyses
id, date, analyseid.

I want to get the newest analyse from Analyses (ordered by date) for every name (they are unique) in Names. I can't really see how to do this without using 2 x nested selects.

My try (Dont get confused about the names. It's the same principle):

SELECT
 B.id,
 B.chosendatetime,
 vStockNames.name
FROM
 vStockNames
INNER JOIN
 (
 SELECT TOP 1
  vAnalysesHistory.id,
  vAnalysesHistory.chosendatetime,
  vAnalysesHistory.companyid
 FROM
  vAnalysesHistory
 ORDER BY
  vAnalysesHistory.chosendatetime DESC
 ) AS B
ON
 B.companyid = vStockNames.stockid

In my example the problem is that i only get 1 row returned (because of top 1). But if I exclude this, I can get multiple analyses of the same name.

Can you help me ? - THanks in advance.

Answer 1

SQL Server 2000+:

 SELECT (SELECT TOP 1 
                a.id
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid 
       ORDER BY a.chosendatetime DESC) AS id,
        n.name, 
        (SELECT TOP 1 
                a.chosendatetime 
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid 
       ORDER BY a.chosendatetime DESC) AS chosendatetime
   FROM vStockNames AS n 

SQL Server 2005+, using CTE:

WITH cte AS (
   SELECT a.id,
          a.date,
          a.analyseid,
          ROW_NUMBER() OVER(PARTITION BY a.analyseid
                                ORDER BY a.date DESC) AS rk
     FROM ANALYSES a)
SELECT n.id, 
       n.name,
       c.date
  FROM NAMES n
  JOIN cte c ON c.analyseid = n.analyseid
            AND c.rk = 1

...without CTE:

SELECT n.id, 
       n.name,
       c.date
  FROM NAMES n
  JOIN (SELECT a.id,
               a.date, 
               a.analyseid,
               ROW_NUMBER() OVER(PARTITION BY a.analyseid
                                     ORDER BY a.date DESC) AS rk
          FROM ANALYSES a) c ON c.analyseid = n.analyseid
                            AND c.rk = 1

Answer 2

You're only asking for the TOP 1, so that's all you're getting. If you want one per companyId, you need to specify that in the SELECT on vAnalysesHistory. Of course, JOINs must be constant and do not allow this. Fortunately, CROSS APPLY comes to the rescue in cases like this.

SELECT
 B.id,
 B.chosendatetime,
 vStockNames.name
FROM
 vStockNames
CROSS APPLY
 (
 SELECT TOP 1
  vAnalysesHistory.id,
  vAnalysesHistory.chosendatetime,
  vAnalysesHistory.companyid
 FROM
  vAnalysesHistory
 WHERE companyid = vStockNames.stockid
 ORDER BY
  vAnalysesHistory.chosendatetime DESC
 ) AS B

You could also use ROW_NUMBER() to do the same:

SELECT
 B.id,
 B.chosendatetime,
 vStockNames.name
FROM
 vStockNames
INNER JOIN
 (
 SELECT
  vAnalysesHistory.id,
  vAnalysesHistory.chosendatetime,
  vAnalysesHistory.companyid,
  ROW_NUMBER() OVER (PARTITION BY companyid ORDER BY chosendatetime DESC) AS row
 FROM
  vAnalysesHistory
 ) AS B
ON
 B.companyid = vStockNames.stockid AND b.row = 1

Personally I'm a fan of the first approach. It will likely be faster and is easier to read IMO.

Answer 3

Will something like this work for you?

;with RankedAnalysesHistory as 
(
 SELECT 
  vah.id,
  vah.chosendatetime,
  vah.companyid
  ,rank() over (partition by vah.companyid order by vah.chosendatetime desc) rnk
 FROM
  vAnalysesHistory vah
)
SELECT
 B.id,
 B.chosendatetime,
 vsn.name
FROM
 vStockNames vsn
 join RankedAnalysesHistory as rah on rah.companyid = vsn.stockid and vah.rnk = 1

Answer 4

It seems to me that you only need SQL-92 for this. Of course, explicit documentation of the joining columns between the tables would help.

Simple names

SELECT B.ID, C.ChosenDate, N.Name
  FROM (SELECT A.AnalyseID, MAX(A.Date) AS ChosenDate
          FROM Analyses AS A
         GROUP BY A.AnalyseID) AS C
  JOIN Analyses AS B ON C.AnalyseID = B.AnalyseID AND C.ChosenDate = B.Date
  JOIN Names    AS N ON N.AnalyseID = C.AnalyseID

The sub-select generates the latest analysis for each company; the join with Analyses picks up the Analyse.ID value corresponding to that latest analysis, and the join with Names picks up the company name. (The C.ChosenDate in the select-list could be replaced by B.Date AS ChosenDate, of course.)

Complicated names

SELECT B.ID, C.ChosenDateTime, N.Name
  FROM (SELECT A.CompanyID, MAX(A.ChosenDateTime) AS ChosenDateTime
          FROM vAnalysesHistory AS A
         GROUP BY A.CompanyID) AS C
  JOIN vAnalysesHistory AS B ON C.CompanyID      = B.CompanyID
                            AND C.ChosenDateTime = B.ChosenDateTime
  JOIN vStockNames AS N ON N.AnalyseID = C.AnalyseID

Same query with systematic renaming (and slightly different layout to avoid horizontal scrollbars).