Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

up vote 6 down vote accepted

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
share|improve this answer
    
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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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