SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can you distinct only a partial query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tranquilraven
Starting Member

19 Posts

Posted - 09/28/2012 :  15:14:41  Show Profile  Reply with Quote
My query is below, what I would like to happen is that the OrderID and CustomerID would only show once, regardless of any data after it, is this even a possibility?



SELECT
Orders.OrderID,
Orders.CustomerID,
Orders.ShipCompanyName,
Orders.ShipFirstName,
Orders.ShipLastName,
Orders.ShipAddress1,
Orders.ShipAddress2,
Orders.ShipCity,
Orders.ShipState,
Orders.ShipPostalCode,
Orders.ShipCountry,
Orders.ShipPhoneNumber,
Orders.ShipFaxNumber,
Orders.ShippingMethodID,
Orders.OrderStatus,
Orders.Shipped,
Orders.ShipDate,
OrderDetails.ProductCode,
OrderDetails.ProductName,
OrderDetails.Quantity,
OrderDetails.Warehouses,
Orders.ShipFirstName + ' ' + Orders.ShipLastName AS fullname
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE Orders.OrderStatus='Processing' AND OrderDetails.Warehouses='2'

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  15:41:10  Show Profile  Reply with Quote
please explain with data what you mean by 'distinct a partial query'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  15:42:41  Show Profile  Reply with Quote
If you are on SQL 2005 or later:
SELECT * FROM 
(
SELECT Orders.OrderID,
       Orders.CustomerID,
       Orders.ShipCompanyName,
       Orders.ShipFirstName,
       Orders.ShipLastName,
       Orders.ShipAddress1,
       Orders.ShipAddress2,
       Orders.ShipCity,
       Orders.ShipState,
       Orders.ShipPostalCode,
       Orders.ShipCountry,
       Orders.ShipPhoneNumber,
       Orders.ShipFaxNumber,
       Orders.ShippingMethodID,
       Orders.OrderStatus,
       Orders.Shipped,
       Orders.ShipDate,
       OrderDetails.ProductCode,
       OrderDetails.ProductName,
       OrderDetails.Quantity,
       OrderDetails.Warehouses,
       Orders.ShipFirstName + ' ' + Orders.ShipLastName AS fullname,
       ROW_NUMBER() OVER (PARTITION BY Orders.OrderID,Orders.CustomerID ORDER BY Orders.ShipDate) AS RN
FROM   Orders
       INNER JOIN OrderDetails
            ON  Orders.OrderID = OrderDetails.OrderID
WHERE  Orders.OrderStatus = 'Processing'
       AND OrderDetails.Warehouses = '2'
) s WHERE RN=1;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000