Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Simple SQL / DB Question!

Posted on 1999-07-17
5
Medium Priority
?
171 Views
Last Modified: 2010-04-06
Hi, I have a Table that looks like so:

Field1 (numeric)          Field2 (varchar 10)
----------------          -------------------
1                         one
1                         one
1                         one
2                         two
2                         two
3                         three

I want to programatically get all the duplicate values in a query that looks something like this:

Label     Number of dups
One       3
Two       2
Three     1

Can someone tell me how to do this with delphi and BDE ?

If I was using access I would do something like

SELECT First(Table1.num) AS [num Field], Count(Table1.num) AS NumberOfDups
FROM Table1
GROUP BY Table1.num
HAVING (((Count(Table1.num))>1));

But these instucctions are not supported by the BDE!

thanks,

Jorge
0
Comment
Question by:jconde2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:simonet
ID: 1389329
Hello, Jorge

Try this:

SELECT count(Field1) as NumOfDups, Field2
from TABLE1
GROUP BY Field2

Note:
1) Field1 and Field2 cannot both be Table1.num.
2) some drivers (actually most drivers) in BDE do not allow formulas in HAVING clauses. You can, however, try this:

HAVING (count(Field1)>1)

3) In BDE Sql youdo not end a statement with ";". Leave that for SQL*Plus !

4) In order to find out that is and what is not supported in Local SQL (the one used by BDE when accessing tables like Paradox, Access, Dbase, etc), take allok at this help file:
 \Program Files\Borland\Common Files\BDE\LocalSQl.hlp

Yours,

Alex


0
 
LVL 15

Accepted Solution

by:
simonet earned 280 total points
ID: 1389330
Jorge,

I tested the HAVING clause using a formula against a Paradox table on BDE and it worked. Thus, the SQL you want is:


SELECT count(Field1) AS NumOfDups, Field2 AS Label
FROM TABLE1
GROUP BY Field2
HAVING (count(Field1)>1)

Remember that in the example above Field1 is the numeric field and field2 is the varchar field.

Yours,

Alex
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1389331
hi jorge,

an other way from my paq

      procedure TForm1.Button1Click(Sender: TObject);
      var
        I : integer;
      begin
        query1.close;
        query1.dataBaseName := Table1.DataBaseName ;  //the TTable, which will examined, must be
      opened
        query1.sql.Clear;                             //Build up SQL-String
        query1.SQL.add('select');                 //Select
        for i := 0 to Table1.FieldCount - 1 do  //all fields
          query1.SQL.add(Table1.Fields[i].FieldName+',');
        query1.SQL.add('count(*) as RecAmount');  //and the amount
        query1.SQL.add('from '+table1.TableName);  //from the table
        query1.SQL.add('group by');                       //grouped by
        for i := 0 to Table1.FieldCount - 2 do           //all fields
          query1.SQL.add(Table1.Fields[i].FieldName+',');
        query1.SQL.add(Table1.Fields[Table1.FieldCount - 1].FieldName);
        query1.SQL.add('Order by RecAmount Desc');   //Descending SortOrder on Duplicates-amount
        query1.open;                                 //establish query
        query1.Filter := 'RecAmount > 1';            //show only duplicates
        query1.Filtered := True;
      end;

meikl
0
 

Author Comment

by:jconde2
ID: 1389332
Thank's barry, meikl!!!!!!!


This seems to have been a dumb question ;-)))

cheers,

jorge
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1389333
barry? you mean alex ;-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question