4

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 record from this table. The 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 please ask.

  • So, you want the max value of ProductId for every OrderId, but the rest of the columns should take the value of the minimum ProductId? – Lamak Jan 30 '12 at 18:02
  • @Lamak I'm going on what he said in the description, not the example data since that makes no sense. – JNK Jan 30 '12 at 18:04
6

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
  • query show error message .Msg 8120, Level 16, State 1, Line 1 Column 'Order Details.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – shamim Jan 30 '12 at 18:20
  • in sub-query syntax 'group by OrderID' fix the error – shamim Jan 30 '12 at 18:22
  • @shamim - my fault, I typed it up too quickly. I added that to the answer – JNK Jan 30 '12 at 18:23
  • JNK thanks for reply.i am new in sql-server or database programming ,i need your helping hand, need some guideline or technique or tips to understand complex queries and want to increase capabilities to solve complex problems.thanks in advanced – shamim Jan 30 '12 at 18:31
6

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
  • +1 for using window functions instead of aggregation – JNK Jan 30 '12 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.