|
|
Posted by Ed Murphy on 05/24/07 16:06
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?
Navigation:
[Reply to this message]
|