CODEWITHSUNDEEP
sqlsql-servermaxouter-join
Thomas
Thomas

Reputation: 33

Joining two sql tables with a one to many relationship, but want the max of the second table

I am trying to join two tables one is a unique feature the seconds is readings taken on several dates that relate to the unique features. I want all of the records in the first table plus the most recent reading. I was able to get the results I was looking for before adding the shape field. By using the code

SELECT
   Table1.Name, Table1.ID, Table1.Shape, 
   Max(Table2.DATE) as Date 
FROM         
   Table1 
LEFT OUTER JOIN
   Table2 ON Table1.ID = table2.ID
GROUP BY
   Table1.Name, Table1.ID, Table1.Shape

The shape field is a geometry type and I get the error

'The type "Geometry" is not comparable. It can not be use in the Group By Clause'

So I need to go about it a different way, but not sure how.

Below is a sample of the two tables and the desired results. 

Table1
Name| ID |Shape
AA1 | 1  | X
BA2 | 2  | Y
CA1 | 3  | Z
CA2 | 4  | Q

Table2
ID | Date
1  | 5/27/2013
1  | 6/27/2014
2  | 5/27/2013
2  | 6/27/2014
3  | 5/27/2013
3  | 6/27/2014

My Desired Result is 

Name| ID |Shape |Date
AA1 | 1  | X    | 6/27/2014
BA2 | 2  | Y    | 6/27/2014
CA1 | 3  | Z    | 6/27/2014
CA2 | 4  | Q    | Null

Upvotes: 3

Views: 412

Answers (3)

JACA_001
JACA_001

Reputation: 1

Try this:

SELECT  t1.Name
        , t1.ID
        , t1.Shape
        , MAX(t2.Date) As Date
FROM    Table1 AS t1
LEFT JOIN Table2 AS t2
        ON  t2.ID = t1.ID
GROUP
    BY  t1.Name
        , t1.ID
        , t1.Shape

Upvotes: 0

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107508

You can do the aggregation on Table2 in a CTE, finding the MAX(DATE) for each ID, and then join that result to Table1:

WITH AggregatedTable2(ID, MaxDate) AS
(
    SELECT 
        ID, MAX(DATE) 
    FROM 
        Table2 
    GROUP BY 
        ID
)
SELECT
    t1.ID, t1.Name, t1.Shape, t2.MaxDate
FROM
    Table1 t1
LEFT JOIN
    AggregatedTable2 t2 ON t1.ID = t2.ID

Upvotes: 2

Greg Viers
Greg Viers

Reputation: 3523

Try casting geometry as a varchar.

Select Table1.Name, Table1.ID, cast(Table1.Shape as varchar(1)) AS Shape, Max(Table2.DATE) as Date 

FROM         Table1 LEFT OUTER JOIN
                      Table2 ON Table1.ID = table2.ID

Group By Table1.Name, Table1.ID,  cast(Table1.Shape as varchar(1))

Upvotes: 0

Related Questions