Prev: Particular query problem
Next: Leading and trailing spaces trimmed with SQL XML
From: fniles on 23 Apr 2010 12:03
How can I write a query that returns to me those records whose
SequenceNumber and Symbol are not unique ? (have more than 1 records whose
combination of SequenceNumber and Symbol are the same)
Thank you

CREATE TABLE [dbo].[TickData1Day](
[SequenceNumber] [char](13) NOT NULL,
[CommodityCode] [char](10) NOT NULL,
[MonthYear] [char](2) NULL,
[Symbol] [varchar](50) NULL,
[OpenPrice] [decimal](16, 4) NULL,
[HighPrice] [decimal](16, 4) NULL,
[LowPrice] [decimal](16, 4) NULL,
[ClosePrice] [decimal](16, 4) NULL,
[Volume] [numeric](18, 0) NULL,
[Date] [datetime] NULL
) ON [PRIMARY]



From: RJ Roberts on 23 Apr 2010 12:57
Assuming Sql Server 2005 or 2008 try the following


With FindDuplicates As
(Select SequenceNumber, Symbol,
MatchCount = Row_Number() over (partition by SequenceNumber, Symbol order
by SequenceNumber)
from dbo.TickData1Day)
Select * from TickData1Day A
Where Exists (select 1 from FindDuplicates where SequenceNumber =
A.SequenceNumber and Symbol = A.Symbol and MatchCount>1)
--
RJ Roberts
DB Architect/Developer


"fniles" wrote:

> How can I write a query that returns to me those records whose
> SequenceNumber and Symbol are not unique ? (have more than 1 records whose
> combination of SequenceNumber and Symbol are the same)
> Thank you
>
> CREATE TABLE [dbo].[TickData1Day](
> [SequenceNumber] [char](13) NOT NULL,
> [CommodityCode] [char](10) NOT NULL,
> [MonthYear] [char](2) NULL,
> [Symbol] [varchar](50) NULL,
> [OpenPrice] [decimal](16, 4) NULL,
> [HighPrice] [decimal](16, 4) NULL,
> [LowPrice] [decimal](16, 4) NULL,
> [ClosePrice] [decimal](16, 4) NULL,
> [Volume] [numeric](18, 0) NULL,
> [Date] [datetime] NULL
> ) ON [PRIMARY]
>
>
>
> .
>
From: Plamen Ratchev on 23 Apr 2010 13:21

Here is one solution:

SELECT SequenceNumber, CommodityCode, Symbol, MonthYear, cnt
FROM (
SELECT SequenceNumber, CommodityCode, Symbol, MonthYear,
COUNT(*) OVER(PARTITION BY SequenceNumber, Symbol) AS cnt
FROM dbo.TickData1Day) AS T
WHERE cnt > 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: fniles on 23 Apr 2010 14:47
Thank you

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:acmdnSaVBNePSkzWnZ2dnUVZ_j4AAAAA(a)speakeasy.net...
>
> Here is one solution:
>
> SELECT SequenceNumber, CommodityCode, Symbol, MonthYear, cnt
> FROM (
> SELECT SequenceNumber, CommodityCode, Symbol, MonthYear,
> COUNT(*) OVER(PARTITION BY SequenceNumber, Symbol) AS cnt
> FROM dbo.TickData1Day) AS T
> WHERE cnt > 1;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: fniles on 23 Apr 2010 14:47
Yes, it's SQL 2005
Thank you

"RJ Roberts" <RJRoberts(a)discussions.microsoft.com> wrote in message
news:33174C81-65A8-4D8B-8D01-2C9572F3B326(a)microsoft.com...
> Assuming Sql Server 2005 or 2008 try the following
>
>
> With FindDuplicates As
> (Select SequenceNumber, Symbol,
> MatchCount = Row_Number() over (partition by SequenceNumber, Symbol order
> by SequenceNumber)
> from dbo.TickData1Day)
> Select * from TickData1Day A
> Where Exists (select 1 from FindDuplicates where SequenceNumber =
> A.SequenceNumber and Symbol = A.Symbol and MatchCount>1)
> --
> RJ Roberts
> DB Architect/Developer
>
>
> "fniles" wrote:
>
>> How can I write a query that returns to me those records whose
>> SequenceNumber and Symbol are not unique ? (have more than 1 records
>> whose
>> combination of SequenceNumber and Symbol are the same)
>> Thank you
>>
>> CREATE TABLE [dbo].[TickData1Day](
>> [SequenceNumber] [char](13) NOT NULL,
>> [CommodityCode] [char](10) NOT NULL,
>> [MonthYear] [char](2) NULL,
>> [Symbol] [varchar](50) NULL,
>> [OpenPrice] [decimal](16, 4) NULL,
>> [HighPrice] [decimal](16, 4) NULL,
>> [LowPrice] [decimal](16, 4) NULL,
>> [ClosePrice] [decimal](16, 4) NULL,
>> [Volume] [numeric](18, 0) NULL,
>> [Date] [datetime] NULL
>> ) ON [PRIMARY]
>>
>>
>>
>> .
>>


 |  Next  |  Last
Pages: 1 2 3
Prev: Particular query problem
Next: Leading and trailing spaces trimmed with SQL XML