how to get unique records

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 has two primary keys, I want to get the unique reco

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

StackExchange Database Administrators Q#11925, answer score: 6