CODEWITHSUNDEEP
delphidelphi-xe2
Glen Morse
Glen Morse

Reputation: 2593

Removing duplicates from an adoquery

I have a adoQuery and would like to fill a listbox with the results, but with no duplicates. 

      with Fdeptlayout.ADOQuery2 do
  begin
    sql.Clear;
         sql.BeginUpdate;
         sql.Add('SELECT');
         sql.Add(' *');
         sql.Add('FROM');
         sql.Add(' `MList`');
         sql.Add(' ORDER BY `Basic Name`');
         sql.EndUpdate;

         open;
  end;

  while not fdeptlayout.ADOquery2.EOF do
    fdeptlayout.ListBox1.Items.Add(fdeptlayout.ADOQuery2['Basic Name']);
end;

Currently this adds 350 items to the listbox, a lot of duplicates. This is too much. How can I alter the query to remove duplicates from the result? :( Any help would be great!

Upvotes: 0

Views: 1417

Answers (2)

GolezTrol
GolezTrol

Reputation: 116170

Change the query:

     sql.Add('SELECT DISTINCT');
     sql.Add('  `Basic Name`');
     sql.Add('FROM');
     sql.Add(' `MList`');
     sql.Add(' ORDER BY `Basic Name`');

DISTINCT filters out duplicates, and by selecting only the field you need, you save fetching possibly a lot of unneeded data. Also, other fields in the records may differ, causing DISTINCT to work sub-optimal.

As a general rule: Don't use * in queries and only select the fields you actually need.

[edit]

And, as agreed in the comments, calling Fdeptlayout.ADOQuery2.Next within the while loop certainly prevents your application locking up. ;-)

Upvotes: 4

Pieter B
Pieter B

Reputation: 1967

You forgot "Next".

  while not fdeptlayout.ADOquery2.EOF do begin
    fdeptlayout.ListBox1.Items.Add(fdeptlayout.ADOQuery2['Basic Name']);
    fdeptlayout.ADOquery2.Next;
  end;

Without next you get an endless loop.

Upvotes: 6

Related Questions