Recent

Author Topic: How to get a list of unique values for a given column from TDataset  (Read 1147 times)

stoffman

  • New Member
  • *
  • Posts: 43
Hi,

Is there a "standard" way to get all the unique values for a given column in TDataset? I can loop my self and find them, but I'm sure I'm not the first one to encounter this problem so I'm looking for more official way.

EDIT: To be sure the data source is not a database, and I explicitly wish to achieve this with data already loaded into a dataset   

Thanks,
« Last Edit: November 04, 2021, 04:49:11 pm by stoffman »

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 780
Re: How to get a list of unique values for a given column from TDataset
« Reply #1 on: November 04, 2021, 03:31:10 pm »
I would add them to a TStringList with duplicates turned off.
« Last Edit: November 04, 2021, 04:03:13 pm by SymbolicFrank »

devEric69

  • Hero Member
  • *****
  • Posts: 646
Re: How to get a list of unique values for a given column from TDataset
« Reply #2 on: November 04, 2021, 03:41:52 pm »
Depending on the database, the SQL keyword DISTINCT or DISCTINCTROW exists.
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

Zvoni

  • Hero Member
  • *****
  • Posts: 819
Re: How to get a list of unique values for a given column from TDataset
« Reply #3 on: November 04, 2021, 04:03:00 pm »
Nevermind, if that column is already defined as "UNIQUE" then you just SELECT it, since the DB enforces uniqueness of values for that column
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

stoffman

  • New Member
  • *
  • Posts: 43
Re: How to get a list of unique values for a given column from TDataset
« Reply #4 on: November 04, 2021, 04:47:14 pm »
@zvoni @devEric69

The data source is not a database.

@SymbolicFrank

That seems like an option. Are there any alternative (assuming the column is not a string and I would like to avoid the conversion) ?

mangakissa

  • Hero Member
  • *****
  • Posts: 1127
Re: How to get a list of unique values for a given column from TDataset
« Reply #5 on: November 05, 2021, 03:46:18 pm »
Hi,

Is there a "standard" way to get all the unique values for a given column in TDataset? I can loop my self and find them, but I'm sure I'm not the first one to encounter this problem so I'm looking for more official way.

EDIT: To be sure the data source is not a database, and I explicitly wish to achieve this with data already loaded into a dataset   

Thanks,
Do you fill the TDataset with a file?
If it is you have to manage it before loading it. If the dataset is already loaded and want to get unique values, SymbolicFrank gives you the only way. Must the data showed in the grid after finding uniqueness?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

stoffman

  • New Member
  • *
  • Posts: 43
Re: How to get a list of unique values for a given column from TDataset
« Reply #6 on: November 05, 2021, 04:37:46 pm »
Do you fill the TDataset with a file?
If it is you have to manage it before loading it. If the dataset is already loaded and want to get unique values, SymbolicFrank gives you the only way. Must the data showed in the grid after finding uniqueness?

Yes, from a file. And as I said I explicitly wish to achieve this after the data already loaded into the dataset.

The dataset may or may not be shown in a Grid. Why does it affect the solution?

avk

  • Hero Member
  • *****
  • Posts: 505
    • my self-education project
Re: How to get a list of unique values for a given column from TDataset
« Reply #7 on: November 05, 2021, 04:46:05 pm »
Didn't check if this would work:
Code: Pascal  [Select][+][-]
  1.  uses
  2.   ..., Generics.Collections;
  3. ...
  4. type
  5.   TVarList = specialize TList<Variant>;
  6.  
  7. function GetUniqList(d: TDataSet; const aFieldName: string): TVarList;
  8. var
  9.   s: specialize THashSet<Variant>;
  10.   v: Variant;
  11. begin
  12.   Result := TVarList.Create;
  13.   s := specialize THashSet<Variant>.Create;
  14.   try
  15.     d.First;
  16.     while not d.EOF do
  17.       begin
  18.         v := d.FieldByName(aFieldName).Value;
  19.         if s.Add(v) then
  20.           Result.Add(v);
  21.         d.Next;
  22.       end;
  23.   finally
  24.     s.Free;
  25.   end;
  26. end;
  27.  
« Last Edit: November 05, 2021, 05:05:09 pm by avk »

 

TinyPortal © 2005-2018