July 7, 2014 at 3:15 pm
I have a view where I join a table A of around 25 columns with a table B. In the SELECT clause, I want to have all columns from table A and MAX on one of the fields of table B. In the AdventureWorks database, the example would be
SELECT
oh.*, MAX(od.UnitPrice)
FROM Sales.SalesOrderHeader oh
INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID=oh.SalesOrderID
GROUP BY od.SalesOrderID
I know this won't work and I need to list all the columns from SalesOrderHeader in the GROUP BY clause. I don't like it because I will need to remember to modify the view each time I change something in SalesOrderHeader. Is there a better way?
July 7, 2014 at 3:23 pm
I believe there is no other way .
July 7, 2014 at 3:34 pm
You might be able to use a different query by using cross apply. However, it might be worse for performance.
The correct option is to write all the columns in the SELECT (instead of the asterisk) and GROUP BY.
July 7, 2014 at 3:34 pm
SELECT
oh.*, o.UnitPrice
FROM Sales.SalesOrderHeader oh
OUTER APPLY (Select top 1 od.unitPrice from Sales.SalesOrderDetail od where od.SalesOrderID=oh.SalesOrderID order by od.unitPrice) as o
I did not test this, but believe that it will work.
July 7, 2014 at 3:35 pm
First, having to keep your schema intact across all dependant objects and avoiding SELECT * queries is a good thing.
However, there are a few approaches. The line by line return:
SELECT
oh.*,
ca.UnitPrice
FROM
Sales.SalesOrderHeader oh
CROSS APPLY
(SELECT TOP 1
UnitPrice
FROMSales.SalesOrderDetailod
WHEREod.SalesOrderID=oh.SalesOrderID
ORDER BY UnitPrice DESC
) AS ca
Fine for a small subset of Order Header, painful for full table returns. This is similar to a correllated subquery (a SELECT inside the SELECT columns), but performs better.
Another option is to use a sub group, like so:
SELECT
oh.*,
drv.MaxUP
FROM
Sales.SalesOrderHeader oh
INNER JOIN
(SELECT
SalesOrderID,
MAX( UnitPrice) AS MaxUP
FROMSales.SalesOrderDetailod
GROUP BY SalesOrderID
) AS drv
ONdrv.SalesOrderID = oh.SalesOrderID
This will perform much better for large pulls, but will depend heavily on your indexing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 7, 2014 at 4:25 pm
Thanks, Craig, the sub group is exactly what I need.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply