|Subject||Re: [firebird-support] How do I count the number of duplicate rows in a table?|
|Author||Svein Erling Tysvær|
>Greetings All,From your description, I'd say the answer is simply
>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)?
SELECT CASE_NUMBER, COUNT(*) CNT
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.