Prev: bcp header row
Next: Team Foundation Server and SQL Server
From: al on 27 Apr 2010 10:06
Hi All,

Hopefully someone can help me with this problem, here's the deal:

I have a table with order details in but it has some duplicated data
that I want to filter out (using a view). The raw data is like this:

Back Order Number Order Number Short Order Date
etc etc
201201 201201
01/01/01
201201/1 201201
06/01/01
201201/2 201201
10/01/01
200203/1 200203
03/03/03

And I need to summarise this so I get a table with unique 'Order
Number Short' values but with the order date (and othe field data)
from the latest line. i.e. for the example above I would like to end
up with:

Order Number Short Order Date etc etc
201201 10/01/01
200203 03/03/03

I hope this makes sense to you, hopefully someone can help.

Thanks in advance.

Alex
From: Plamen Ratchev on 27 Apr 2010 10:51
Try this:

SELECT back_order_number, order_number_short, order_date, <other_columns>,
FROM (
SELECT back_order_number, order_number_short, order_date, <other_columns>,
ROW_NUMBER() OVER(PARTITION BY order_number_short ORDER BY order_date DESC) AS rk
FROM Orders) AS O
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: al on 28 Apr 2010 03:38
Hi Plamen,

I think that gives me roughly what I need, many thanks for your help
and prompt reply.

Alex.


On 27 Apr, 15:51, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Try this:
>
> SELECT back_order_number, order_number_short, order_date, <other_columns>,
> FROM (
> SELECT back_order_number, order_number_short, order_date, <other_columns>,
>         ROW_NUMBER() OVER(PARTITION BY order_number_short ORDER BY order_date DESC) AS rk
> FROM Orders) AS O
> WHERE rk = 1;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

 | 
Pages: 1
Prev: bcp header row
Next: Team Foundation Server and SQL Server