benzzon.se
Published on benzzon.se (http://www.benzzon.se)

Home > SQL: Joins, group by etc..

1 post / 0 new
Log in [1] to post comments
Wed, 03/15/2006 - 22:40
#1 [2]
benzzon
benzzon's picture
SQL: Joins, group by etc..

--
-- Selectsats för att returnera parent-child data, där unika rader
-- önskas i resultatet. För child-data returneras endast den post
-- som har max/högsta id för kolumnen "ProductID" i exemplet.
--
-- Scriptet visar även data för child-kolumnen "UnitPrice" som
-- hör till child-posten med max/högsta id.
--

USE Northwind
GO

SELECT Parent.OrderID, Child.ProductID, Child.UnitPrice
FROM [Orders] Parent
 INNER JOIN
  (SELECT Child.OrderID, Child.ProductID, Child.UnitPrice
    FROM [Order Details] Child
     INNER JOIN
      (SELECT OrderID , MAX(ProductID) AS ProductID FROM [Order Details] GROUP BY OrderID) maxchild
     ON Child.OrderID = maxchild.OrderID
        AND Child.ProductID = maxchild.ProductID
      ) Child
 ON Parent.OrderID = Child.OrderID
ORDER BY
Parent.OrderID

GO

Top

Source URL: http://www.benzzon.se/?q=node/112

Links
[1] http://www.benzzon.se/?q=user/login&destination=node/112%23comment-form
[2] http://www.benzzon.se/?q=node/112