Remove neighbouring duplicates

[mGracz]

Welcome,

how can I alter following table in order to reduce neighbouring
duplicates (symbol, position, quantity, price).

Nr Symbol Position Quantity
Price Date
1. wz9999b 1 1.0
2500.0 2007-05-09 08:09:42.653
2. wz9999b 2 12.0
2500.0 2007-05-09 08:09:42.653
3. wz9999b 1 100.0
2590.0 2007-05-10 15:47:04.140
4. PZ0008VX 1 2280.884 2090.5500000000002 2007-05-16
12:43:12.403
5. PZ0008VX 1 2280.884 2102.0500000000002 2007-05-16
12:45:27.420
6. wz9999b 1 0.001
2500.0 2007-05-18 09:47:16.033
7. wz9999b 1 0.001
2500.0 2007-05-18 09:47:53.270
8. wz9999b 1 1.0
1.0 2007-05-22 12:35:07.893
9. PZ0008VX 1 2280.884 2102.0500000000002 2007-05-24
09:38:26.160
10. PZ0008VX 1 2280.884 2102.0500000000002 2007-05-24
09:38:38.800
11. wz9999b 1 0.001 2500.0
2007-05-24 12:35:07.207
12 wz9999b 1 0.002 2500.0
2007-05-24 12:35:14.987
13. wz9999b 1 0.001 2500.0
2007-05-24 12:38:07.207

In the result set I would like to get the rows number 6 and 10.

Any suggestions??

[Ed Murphy]

mGracz wrote:

how can I alter following table in order to reduce neighbouring
duplicates (symbol, position, quantity, price).

Nr Symbol Position Quantity
Price Date
1. wz9999b 1 1.0
2500.0 2007-05-09 08:09:42.653
2. wz9999b 2 12.0
2500.0 2007-05-09 08:09:42.653
3. wz9999b 1 100.0
2590.0 2007-05-10 15:47:04.140
4. PZ0008VX 1 2280.884 2090.5500000000002 2007-05-16
12:43:12.403
5. PZ0008VX 1 2280.884 2102.0500000000002 2007-05-16
12:45:27.420
6. wz9999b 1 0.001
2500.0 2007-05-18 09:47:16.033
7. wz9999b 1 0.001
2500.0 2007-05-18 09:47:53.270
8. wz9999b 1 1.0
1.0 2007-05-22 12:35:07.893
9. PZ0008VX 1 2280.884 2102.0500000000002 2007-05-24
09:38:26.160
10. PZ0008VX 1 2280.884 2102.0500000000002 2007-05-24
09:38:38.800
11. wz9999b 1 0.001 2500.0
2007-05-24 12:35:07.207
12 wz9999b 1 0.002 2500.0
2007-05-24 12:35:14.987
13. wz9999b 1 0.001 2500.0
2007-05-24 12:38:07.207

In the result set I would like to get the rows number 6 and 10.

Assuming that row numbers are not stored in the table, but indicate
sorting on (Date, Position):

select a.Symbol, a.Position, a.Quantity, a.Price
from the_table a
join the_table b on a.Symbol = b.Symbol
and a.Position = b.Position
and a.Quantity = b.Quantity
and a.Price = b.Price
and a.Date < b.Date
left join the_table c on a.Date < c.Date
and c.Date < b.Date
where c.Date is null

But why do you require Position to match? Suppose you had this:

Symbol | Position | Quantity | Price | Date
--------+----------+----------+--------+------------------------
wz9999b | 1 | 1.0 | 2500.0 | 2007-05-09 08:09:42.653
wz9999b | 2 | 12.0 | 2500.0 | 2007-05-09 08:09:42.653
wz9999b | 1 | 12.0 | 2500.0 | 2007-05-10 15:47:04.140
wz99995 | 2 | 100.0 | 2590.0 | 2007-05-10 15:47:04.140

would the two rows with Quantity = 12.0 count as duplicates? Why
or why not?

[Erland Sommarskog]

mGracz (M.************@gmail.com) writes:

Welcome,

how can I alter following table in order to reduce neighbouring
duplicates (symbol, position, quantity, price).
...
In the result set I would like to get the rows number 6 and 10.

Any suggestions??

Since you did not say which version of SQL Server you are using, I
will assume SQL 2005, because the query is a lot easier to write
on SQL 2005. And performance will be a lot better.

WITH numbered_items (rownum, symbol, position, qty, price, date)
SELECT rownum = row_number() OVER (
PARTITION BY Symbol, Position, Quantity, Price
ORDER BY Date),
Symbol, Position, Quantity, Date
FROM tbl
)
SELECT a.symbol, a.position, a.qty, a.date
FROM numbered_items a
JOIN numbered_items b ON a.symbol = b.symbol
AND a.position = b.positon
AND a.qty = b.qty
AND a.price = b.price
AND a.rownum = b.rownum - 1
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx