EDB SQL includes the really useful extension "LIST", which aggregates all the values in a column into a CLOB data-coloum with the values separated by a comma.

SELECT
 CompanyName,
 LIST(ContactName) as ContactNames

FROM Companies C
LEFT JON CompanyContacts CC ON CC.CompaniesID = C.ID

GROUP BY CompanyName

--

The LIST returns all the ContactNames in the order they occur in the table.

Is it possible to return the ContactNames in alphabetical order? For example by applying an index to the CompanyContacts prior to calling the query?

My requirement isn't totally trivial (although it is nice to have the data in the "right" order), I also want to be able to compare sets of LISTed data and see whether they are identical. If I cannot control the ordering of the lists this is not easily possible.

Adam

Sorry I figured it out. Create a View on CompanyContacts which orders the data neatly. Then the subsequent list is orderly ...

Adam

Glad you've found a solution. I thought this

SELECT LIST(_Forename,#13+#10) AS Pinner FROM
(SELECT  _Forename FROM Contacts ORDER BY _Forename) AS XX

would work but it doesn't in 2.11b3 or 2.12b1

I have a feeling it did work at some point. I'm sure Tim will let me know when he's here next.

Roy Lambert

My optimism was premature. I _thought_ it would work, it made sense that it should, but strangely it doesn't.

The view is ordered (of course), but when the data is pulled from that view into a new view with the LIST criteria the ordering pops back into the ordering which was present in the original table!

I will try replacing my primary key with a primary key based on the fields I want ordered ... I am pretty sure that will fix it. If that fails I check your suggestion ... thanks.

Stranger ... applying a PK to the table doesn't seem to affect the ordering of the LIST, even after dropping the original test data and re-inserting it!

Items are added to the list in the order in which they were added to the table, regardless of the Primary Key.

The only way I can see around it would be to create a "SORT" function which could iterate the LIST, probably as an external module.

Adam


As I said I sort of remember this working. Hunting through all the posts & emails I found this

select list(_companyname) as CompanyName
from (select distinct _companyname from invoices order by _companyname) AS
DistinctCompany

from Tim back in 2009 so I assume it produced an ordered list back then. I'll give him a nudge

Roy Lambert

Roy,

<< I have a feeling it did work at some point. I'm sure Tim will let me know
when he's here next. >>

It may have, but that point is kind of irrelevant in this discussion.  The
issue is this: when a table is referenced in any SQL DML statement, it is a
"set of rows" and nothing more.  You cannot make any assumptions about how,
or in what order, EDB is going to read the rows in order to satisfy the the
SQL statement.  This goes for derived tables and views that are accessed in
the same manner.  When you use an ORDER BY with a derived table, you're
simply saying that you want the table ordered for the purposes of the SQL
statement for the derived table *only* (important for ranges, for example).
It has absolutely *no* bearing on how the rows are read by any outer SQL
statements.

Also, please don't ask to have this changed - I've been through it a million
times and the performance absolutely goes in the toilet for derived table
selection when you force EDB to respect the ORDER BY when it shouldn't and
doesn't have any obligation to do so.  EDB doesn't select from base tables
with any active index assumptions, and it shouldn't for derived
tables/views, either.

Now, the real issue here is why the LIST function is not producing
automatically-sorted results.  *That*, I can fix for the next build.  It
keeps the listing sorted anyways as it is processed.  It just simply doesn't
go back and "fix-up" the ordering afterwards, and it can do so.

Tim Young
Elevate Software
www.elevatesoft.com

<< Now, the real issue here is why the LIST function is not producing
automatically-sorted results.  *That*, I can fix for the next build.  It
keeps the listing sorted anyways as it is processed.  It just simply doesn't
go back and "fix-up" the ordering afterwards, and it can do so. >>

Okay, here's what I've added for the next build.  Now you can just use
something like this:

select list(ordered Company) FROM customer

If you want it distinct and ordered, you would simply use this:

select list(distinct ordered Company) FROM customer

I used "ordered" instead of "order by" to keep things less confusing with
really complicated SQL statements.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< I have a feeling it did work at some point. I'm sure Tim will let me know
>when he's here next. >>
>
>It may have, but that point is kind of irrelevant in this discussion. The
>issue is this: when a table is referenced in any SQL DML statement, it is a
>"set of rows" and nothing more. You cannot make any assumptions about how,
>or in what order, EDB is going to read the rows in order to satisfy the the
>SQL statement. This goes for derived tables and views that are accessed in
>the same manner. When you use an ORDER BY with a derived table, you're
>simply saying that you want the table ordered for the purposes of the SQL
>statement for the derived table *only* (important for ranges, for example).
>It has absolutely *no* bearing on how the rows are read by any outer SQL
>statements.
>
>Also, please don't ask to have this changed - I've been through it a million
>times and the performance absolutely goes in the toilet for derived table
>selection when you force EDB to respect the ORDER BY when it shouldn't and
>doesn't have any obligation to do so. EDB doesn't select from base tables
>with any active index assumptions, and it shouldn't for derived
>tables/views, either.

I'll happily accept that but its rather counter intuitive to be able to enter ORDER BY and have it ignored in some circiumstances.

>Now, the real issue here is why the LIST function is not producing
>automatically-sorted results. *That*, I can fix for the next build. It
>keeps the listing sorted anyways as it is processed. It just simply doesn't
>go back and "fix-up" the ordering afterwards, and it can do so.

Thanks

Roy

Tim


><< Now, the real issue here is why the LIST function is not producing
>automatically-sorted results. *That*, I can fix for the next build. It
>keeps the listing sorted anyways as it is processed. It just simply doesn't
>go back and "fix-up" the ordering afterwards, and it can do so. >>

I think I understand that statement but I'm very happy with what you've proposed below anyway.

>Okay, here's what I've added for the next build. Now you can just use
>something like this:
>
>select list(ordered Company) FROM customer
>
>If you want it distinct and ordered, you would simply use this:
>
>select list(distinct ordered Company) FROM customer

Fantastic two enhancements for the price of one.

>I used "ordered" instead of "order by" to keep things less confusing with
>really complicated SQL statements.

ROFL

>If you have any other questions, please let me know.

Only a trivial one - which direction does it sort?

Roy