TSQL Left Join with multiple right hand rows?

I believe you need this select T. ColA, T. ColB, T.

ColC from T inner join (select ColA, max(TopColumn) MaxTopColumn from T group by ColA) OrderedTable on T. ColA = OrderedTable. ColA and T.

TopColumn = OrderedTable. MaxTopColumn Fairly common query for versioned tables, requires an inner join to a max query The table name "Table" doesn't help matters, I've renamed it T.

I believe you need this... select T. ColA, T. ColB, T.

ColC from T inner join (select ColA, max(TopColumn) MaxTopColumn from T group by ColA) OrderedTable on T. ColA = OrderedTable. ColA and T.

TopColumn = OrderedTable. MaxTopColumn Fairly common query for versioned tables, requires an inner join to a max query. The table name "Table" doesn't help matters, I've renamed it T.

– Spence Jul 11 '09 at 17:26 You aggregate by the non-aggregate columns in a query. I don't have a sql server to hand but I have written this query dozens of times on MSQLServer and Sybase and this is the correct structure. The downside is the aggregate query is a table scan (or full index scan) and there is no way around it, the performance isn't perfect, but for what you are asking this is the query.

– polyglot Jul 11 '09 at 17:28 What you are trying to do is return the rows from T, and restrict the rows from T on the basis of MaxTopColumn. Without specifying it as a join column T will not be restricted. – polyglot Jul 11 '09 at 17:29 Table scan makes sense, but I'm working with an existing schema, so cheers for your help :).

– Spence Jul 11 '09 at 17:31.

Not that simple. The LEFT JOIN returns all matching right-hand rows. So the question on guarantee here is not really relevant.

You'd have to do something with a subquery to get the single row you need, using TOP 1 in the subquery.

But I can't use Top 1 as an aggregate. This is solely to solve the problem of fetching the top row FOR EACH ColA which I am trying to avoid a cursor to do. – Spence Jul 11 '09 at 17:11 @David M: "The LEFT JOIN returns all matching right-hand rows" - isn't that the wrong way around?

– Mitch Wheat Jul 11 '09 at 17:13 1 No. A LEFT JOIN returns all rows from the left, and all matching rows from the right. – David M Jul 11 '09 at 17:22 A left outer join will return one row for every row in the table on the left, with the joined columns set to null or a value.

Regardless of the number of rows int he right hand side, it will only return a row for every row on the left. AFAIK. – Spence Jul 11 '09 at 17:31 2 Sorry, not correct.

If there are multiple matching rows on the right, multiple rows will be returned. – David M Jul 11 '09 at 18:19.

A LEFT JOIN returns all left hand rows satisfying any WHERE criteria regardless of whether there is a matching row on the right (on the join key(s)). Columns in the right table will be returned as NULL where there is no match on join key.

Understood, I've clarified my question in that it is a self join. As such the query will not have nulls in the right hand side. Very valid point though.

– Spence Jul 11 '09 at 17:16.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.