Re: Sql question

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

And to add the number of duplicates, add COUNT(*) to the SELECT statement:

SELECT fclrid, frecno, count(*) as dupcount FROM Ypexfile WHERE fclass = 
'7C' and frecid = 'H'
GROUP BY (fclrid,frecno) HAVING count(*) > 1

Vern

At 02:26 PM 11/16/2004, you wrote:

> Use the GROUP BY and HAVING clauses.
>
> Something like
>
>  SELECT fclrid, frecno FROM Ypexfile WHERE fclass = '7C' and frecid = 'H'
> GROUP BY (fclrid,frecno) HAVING count(*) > 1
>
> Thanks,
>
> Mark
>
> Mark D. Walter
> Senior Programmer/Analyst
> CCX, Inc.
> mwalter@xxxxxxxxxx
> http://www.ccxinc.com
>
>
> |---------+--------------------------------->
> |         |           "Muralidhar Narayana" |
> |         |           <Muralidhar_Narayana@i|
> |         |           nfosys.com>           |
> |         |           Sent by:              |
> |         |           midrange-l-bounces@mid|
> |         |           range.com             |
> |         |                                 |
> |         |                                 |
> |         |           11/16/2004 03:18 PM   |
> |         |           Please respond to     |
> |         |           Midrange Systems      |
> |         |           Technical Discussion  |
> |---------+--------------------------------->
>
>  >------------------------------------------------------------------------------------------------------------------------------|
>   | 
>                                                         |
>   |       To:       <midrange-l@xxxxxxxxxxxx> 
>                                                         |
>   |       cc: 
>                                                         |
>   |       Subject:  Sql 
> question 
> |
>
>  
> >------------------------------------------------------------------------------------------------------------------------------|
>
>
>
>
> Iam just doing below query, but got whole bunch of records, I basically
> want to find duplicate records.
>
> SELECT FCLRID, FRECNO FROM YPEXFILE  WHERE  FCLASS= '7C' and FRECID =
>
> 'H' ORDER BY FCLRID
>
> Any duplicate records with respect to FRECNO(I mean i want to find if
> FRECNO is same for more than one record)..
>
> Can someone give me pointer...
>
> Thanks,
>
>
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.