Select DISTINCT in ZMSQL component
lazarus / 16-Databases

----------------------------------------------------------------------
maurobio · 2021-09-05 15:44

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

----------------------------------------------------------------------
paweld · 2021-09-05 16:55

Use GROUP BY https://github.com/git-
bee/janSQL/blob/master/doc/5_sql_syntax.md#512-group-by

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT name
FROM TABLE GROUP BY name--instead of: select distinct name from table

----------------------------------------------------------------------
maurobio · 2021-09-05 18:19

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,

----------------------------------------------------------------------
maurobio · 2021-09-06 10:48

Dear @paweld,

... however. it does not work! 

The query:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
PRODUCER FROM cellar GROUP BY PRODUCER
should results in:

QuotePRODUCER
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,

----------------------------------------------------------------------
balazsszekely · 2021-09-06 11:16

Hi maurobio,

Use "Group by" in combination with an aggregate functions. Like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ZMQueryDataSet1.SQL.Text := 'select sum(bin), producer from cellar
group by producer order by producer';

----------------------------------------------------------------------
maurobio · 2021-09-06 11:21

@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,

----------------------------------------------------------------------
paweld · 2021-09-06 19:28

version that does not require aggregate functions:
https://github.com/paweld/zmsql/tree/master

----------------------------------------------------------------------
maurobio · 2021-09-06 19:32

Dear @paweld,

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

With best wishes,

----------------------------------------------------------------------
maurobio · 2021-09-07 00:47

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,

----------------------------------------------------------------------
funlw65 · 2021-09-12 20:52

Then, will ZMSQL be updated for the Online Package Manager?
Thank you!

----------------------------------------------------------------------
maurobio · 2021-09-12 23:08

Hi, @funlw65!

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

With best wishes,

----------------------------------------------------------------------
paweld · 2021-09-14 17:56

@funlw65 @msutobio : done.
Very thanks to @wp

----------------------------------------------------------------------
maurobio · 2021-09-14 18:14

@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,

----------------------------------------------------------------------
funlw65 · 2021-09-16 04:54

Quote from: paweld on September 14, 2021, 07:56:22 pm@funlw65
@msutobio : done.
Very thanks to @wp

Thank you very much guys!

----------------------------------------------------------------------
scons · 2021-10-01 15:33

also my thanks to all for the update 👍🏻