From: al on
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
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
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