Recent

Author Topic: Select DISTINCT in ZMSQL component  (Read 3750 times)

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Select DISTINCT in ZMSQL component
« on: September 05, 2021, 05:44:51 pm »
Dear ALL,

I have ben experimenting with the ZMSQL component (https://wiki.lazarus.freepascal.org/ZMSQL), which is really great and almost perfect for my needs. It uses the janSQL engine for querying the data which unfortunately lacks support to the DISTINCT clause in SELECT statements (https://www.w3schools.com/sql/sql_distinct.asp).

Development of the ZMSQL component seems to be stalled; the last update has been four years ago, and the most recent forum activity was in 2015 (https://forum.lazarus.freepascal.org/index.php/topic,13821.105.html), so I am afraid that no new features are expected to be added to this component at this time.

Any ideas on how the behaviour of the SELECT DISTINCT clause could be 'emulated' using a ZMSQL query dataset?

Thanks in advance.

With best wishes
« Last Edit: September 06, 2021, 12:42:35 pm by maurobio »
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

paweld

  • Sr. Member
  • ****
  • Posts: 312
Re: Select DISTINCT in ZMSQL component
« Reply #1 on: September 05, 2021, 06:55:40 pm »
Use GROUP BY https://github.com/git-bee/janSQL/blob/master/doc/5_sql_syntax.md#512-group-by

Code: SQL  [Select][+][-]
  1. SELECT name FROM TABLE GROUP BY name
  2. --instead of: select distinct name from table
  3.  
Best regards
paweld

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Re: Select DISTINCT in ZMSQL component
« Reply #2 on: September 05, 2021, 08:19:54 pm »
Dear @paweld,

Thanks a lot! Your tip not only solved my problem, but also helped me to learn something new about SQL - in decades using it, I have never paid attention to the essential similarity between SELECT DISTINCT and GROUP BY! Living and learning...

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Re: Select DISTINCT in ZMSQL component
« Reply #3 on: September 06, 2021, 12:48:21 pm »
Dear @paweld,

... however. it does not work!  :(

The query:

Code: Pascal  [Select][+][-]
  1. SELECT PRODUCER FROM cellar GROUP BY PRODUCER

should results in:

Quote
PRODUCER
Buena Vista
Ch.St.Jean
Chappellet
Jekel Vineyard
Louis Martini
Mirassou
Robt.Mondavi
Sattui
but in fact it just returns a list of all producers with duplicates. See the attached screenshot and sample application (which includes the CELLAR database).

It looks like the jansSQL engine works only when the clause GROUP BY is used with aggregate functions.

Any hints?

Thanks in advance!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

GetMem

  • Hero Member
  • *****
  • Posts: 3493
Re: Select DISTINCT in ZMSQL component
« Reply #4 on: September 06, 2021, 01:16:06 pm »
Hi maurobio,

Use "Group by" in combination with an aggregate functions. Like this:
Code: Pascal  [Select][+][-]
  1. ZMQueryDataSet1.SQL.Text := 'select sum(bin), producer from cellar group by producer order by producer';  

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Re: Select DISTINCT in ZMSQL component
« Reply #5 on: September 06, 2021, 01:21:52 pm »
@GetMem,

Thanks, as I had already pointed out in my previous post, the GROUP BY clause in janSQL engine works only with aggregate functions; unfortunately this does not entirely suits me. It looks like I will have to resort to SQLite anyway (where at least I can use SELECT DISTINCT, that is much clear)...

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

paweld

  • Sr. Member
  • ****
  • Posts: 312
Re: Select DISTINCT in ZMSQL component
« Reply #6 on: September 06, 2021, 09:28:12 pm »
version that does not require aggregate functions: https://github.com/paweld/zmsql/tree/master
Best regards
paweld

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Re: Select DISTINCT in ZMSQL component
« Reply #7 on: September 06, 2021, 09:32:51 pm »
Dear @paweld,

Thank you very much! I will give it a try and report the results later.

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Re: Select DISTINCT in ZMSQL component
« Reply #8 on: September 07, 2021, 02:47:31 am »
Dear @paweld,

With the modifications you have introduced, the GROUP BY clause now works fine (with our without aggregate functions, as it should be).

Thanks a lot!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

funlw65

  • Jr. Member
  • **
  • Posts: 79
    • Visual Pin Configurator for Nucleo 64pin boards
Re: Select DISTINCT in ZMSQL component
« Reply #9 on: September 12, 2021, 10:52:28 pm »
Then, will ZMSQL be updated for the Online Package Manager?
Thank you!
Lazarus 2.3.0 fpc 3.2.2, Linux, UNIX BSD
widgetset: gtk2, msegui, qt5

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Re: Select DISTINCT in ZMSQL component
« Reply #10 on: September 13, 2021, 01:08:34 am »
Hi, @funlw65!

I hope @paweld will take care of that! The updated version should be made available to all.

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

paweld

  • Sr. Member
  • ****
  • Posts: 312
Re: Select DISTINCT in ZMSQL component
« Reply #11 on: September 14, 2021, 07:56:22 pm »
@funlw65 @msutobio : done.
Very thanks to @wp
Best regards
paweld

maurobio

  • Sr. Member
  • ****
  • Posts: 301
  • Ecology is everything.
    • GitHub
Re: Select DISTINCT in ZMSQL component
« Reply #12 on: September 14, 2021, 08:14:46 pm »
@paweld,

Yes, @wp is surely amongst my heroes! Many thanks to him and all congratulations for his fantastic work with FPSpreadSheet and FPSpreadSheetDataset!

Thanks also to you, @paweld, who improved in a decisive way the already great ZMSQL component!

Since I am using these components to write a (free, open source) cross-platform application for handling ecological data, you are doing your part to save the world (because ecology is everything)!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

funlw65

  • Jr. Member
  • **
  • Posts: 79
    • Visual Pin Configurator for Nucleo 64pin boards
Re: Select DISTINCT in ZMSQL component
« Reply #13 on: September 16, 2021, 06:54:59 am »
@funlw65 @msutobio : done.
Very thanks to @wp

Thank you very much guys!
Lazarus 2.3.0 fpc 3.2.2, Linux, UNIX BSD
widgetset: gtk2, msegui, qt5

scons

  • Full Member
  • ***
  • Posts: 141
Re: Select DISTINCT in ZMSQL component
« Reply #14 on: October 01, 2021, 05:33:09 pm »
also my thanks to all for the update 👍🏻
Windows 10-64bit Lazarus 2.0.12 + FPC 3.2.0

 

TinyPortal © 2005-2018