----- Original Message -----

From: Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]

To: firebird-support@yahoogroups.com

Sent: Tuesday, October 28, 2014 7:23 AM

Subject: Re: [firebird-support] How do I count the number of duplicate rows in a table?

 

>Greetings All,
>
>Firebird 1.5.3
>
>Should be elementary but, I'm drawing a blank on how to accomplish this.
>
>I have a table (ACCT_CASE_COURT) that contains these fields (among others):
>
>ACCT_CASE_COURT_ID INTEGER NOT NULL PK
>ACCT_ID INTEGER NOT NULL
>CASE_ID SMALLINT NOT NULL
>CASE_NUMBER VARCHAR(20) NOT NULL
>...
>
>How do I form a SQL Select statement that would contain CASE_NUMBER in the first column (sorted) and
>number of times that the case number is found in the table in the second column (CNT)?

From your description, I'd say the answer is simply

SELECT CASE_NUMBER, COUNT(*) CNT
FROM ACCT_CASE_COURT
GROUP BY CASE_NUMBER

(feel free to add ORDER BY CASE_NUMBER, I didn't since all existing Firebird versions happens to do it implicitly when having GROUP BY).

If this is not the answer, then please try to formulate your question differently so that we understand what you want.

HTH,
Set

---

This email is free from viruses and malware because avast! Antivirus protection is active.