Re: SQL query help

From: <Amritha.Datta_at_gmail.com>
Date: 11 Aug 2006 10:33:28 -0700
Message-ID: <1155317608.405768.130700@i3g2000cwc.googlegroups.com>

Thanks Maxim. This works!
Would like to read more about PARTITION BY Syntax. Could you provide some links to see the details.

Thanks.

Maxim Demenko wrote:
> Amritha.Datta_at_gmail.com schrieb:
> > Can anyone help me to get results from this tricky query:
> >
> > Select Recnum, trim(Both From Family_ID), trim(Both From DOB)
> > From abc
> > Where File_Key = 2470 And Sub_File_Key = 1 and Reason is null and
> > trim(Both From Family_ID) IN
> > (
> > Select trim(Both From Family_ID) From abc Where
> > File_Key = 2470 And Sub_File_Key = 1 and Reason is null
> > And trim(Both From Family_ID) IN
> > (
> > select trim(Both From Family_ID) From abc Where
> > File_Key = 2470 And Sub_File_Key = 1 and reason is null
> > group by trim(Both From Family_ID) having count(trim(Both From
> > Family_ID)) > 1
> > )
> > group by trim(Both From Family_ID), trim(Both From DOB) having
> > count(trim(Both From DOB)) > 1
> > )
> > group by trim(Both From Family_ID), trim(Both From DOB) having
> > count(trim(Both From Family_ID) || trim(Both From DOB)) > 1
> >
> > I know I should not use group by with out all the fields listed in
> > select statement. But my intension is to have Recnum based on these
> > conditions.
> >
> > Can anyone come across this kinda issue? Is there any work arrond on
> > this ?
> >
> >
> > Thanks.
> >

>

> At first look i have the impression (which may of course be wrong), that
> should do what you ask.
> SELECT Recnum
> FROM (SELECT Recnum,
> COUNT(*) Over(PARTITION BY TRIM(Family_Id), TRIM(Dob)) Cnt
> FROM Abc
> WHERE File_Key = 2470
> AND Sub_File_Key = 1
> AND Reason IS NULL)
> WHERE Cnt > 1
>
>
> Best regards
>
> Maxim
Received on Fri Aug 11 2006 - 12:33:28 CDT