How To Select The Latest Records Using 2 Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How To Select The Latest Records Using 2 Tables

Author

Topic

winterminute
Starting Member

1 Post

Posted - 2004-02-11 : 16:04:58

Here's what I am trying to do.
I have two tables with the following structure:
GROUPS:
ID | NAME | TARGETCOUNT

DATAPOINTS:
DATAPOINTID | TIMEDATESTAMP | GROUPID | COUNT

Basically, the GROUPID in DataPoints matches up with one record (and one record only) in the GROUPS table.
I need to be able to get the latest COUNT for each of the GROUPS.

Here's what I have so far:
SELECT *
FROM Groups
INNER JOIN
(SELECT GroupID, MAX(TIMEDATESTAMP ) as Latest
FROM DATAPOINTS
GROUP BY GROUPID ) as Count
ON Groups.ID= Count.GroupID

However, but I don't get the field "COUNT" with that query and I can't add it to the inner SELECT since then I will have to include in the GROUPBY. So, I think I need another JOIN and another inner query, but not sure how that should be structured.

Make sense?

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-11 : 16:43:51

Here's a sample you can cut and paste in to QA....

Uderstand that in the 1 to many relationship, the parental rows will be repeated...


USE Northwind
GO
    SELECT * 
      FROM Orders o
INNER JOIN [Order Details] d
	ON o.OrderId = d.OrderId
     WHERE EXISTS (SELECT * 
		     FROM Orders x 
		 GROUP BY OrderId 
	           HAVING MAX(x.OrderDate) = o.OrderDate 
		      AND x.OrderId = o.OrderId)
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources