Re: Remove neighbouring duplicates
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 24 May 2007 21:50:19 +0000 (UTC)
mGracz (M.Graczykowski@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Remove neighbouring duplicates
- From: mGracz
- Remove neighbouring duplicates
- Prev by Date: Use CVS server with SQL Server
- Next by Date: Re: Use CVS server with SQL Server
- Previous by thread: Re: Remove neighbouring duplicates
- Next by thread: Re: Any Input: Converting from Sybase to SQL
- Index(es):
Relevant Pages
|
