|
|
Posted by Erland Sommarskog on 05/24/07 21:50
mGracz (M.Graczykowski@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|