how to get unique records

Question

Table structure

CREATE TABLE [dbo].[Order Details2](
    [OrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL   DEFAULT (0),
    [Quantity] [smallint] NOT NULL   DEFAULT (1),
    [Discount] [real] NOT NULL   DEFAULT (0),
 CONSTRAINT [PK_Order_Details2] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC,
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Table values

OrderID ProductID   UnitePrice  Quantity    Discount
10248   11  14  12  0
10248   42  9.8 10  0
10248   72  34.8    5   0
10249   14  18.6    9   0
10249   51  42.4    40  0
10250   41  7.7 10  0
10250   51  42.4    35  0.15
10250   65  16.8    15  0.15
10251   22  16.8    6   0.05
10251   57  15.6    15  0.05
10251   65  16.8    20  0

Required output

OrderID ProductID   UnitePrice  Quantity    Discount
10248   72  14  12  0
10249   51  18.6    9   0
10250   65  7.7 10  0

My table have two primary key ,I want unique record from this table .required output contain column orderid and max productid row (just one row)only. I need helping hand to solve this issue.

If have any query plz ask. thanks in advanced.

Answer 1

Just JOIN on a subquery:

SELECT *
FROM MyTable T
INNER JOIN (SELECT OrderID, MAX(ProductID) as ProductID
            FROM MyTable
            GROUP BY OrderID) x
  ON x.orderid = t.orderid
  AND x.productid = t.productid

Answer 2

Well, as @JNK said, what you explained on your question is different to what you showed us in your example. So, if I follow you required output, then you can try this query:

SELECT *
FROM (  SELECT  OrderID, MAX(ProductId) OVER(PARTITION BY OrderId) ProductId, 
                UnitePrice, Quantity, Discount, 
                ROW_NUMBER() OVER(PARTITION BY OrderId ORDER BY ProductId) Corr
        FROM #Temp1) Data
WHERE Corr = 1