The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

When using the wonderful LIST aggregate with a rather complex expression, how can I specify the order within the list?

I want to have the list ordered by the complex expression itself but would not want to repeat the whole term.

Something like

select list(expr1 || ' ' || expr2 || ' ' || expr3 || ' ' || expr4, ', ' order by 1)

would be sufficient, but unfortunately, order-by ordinal (here: 1) is not allowed for list. Neither is it allowed to give the complex expression an alias and use that as order-by expression. As to the docs, I seem to be allowed to use an integral variable ("order by MyInt") but as I'm using a view, that possibility seems to fail, too. Repeating the whole expression does work, but I would like to avoid that.

Any hints for a "shorter syntax" are highly appreciated:) - I'm using SA


If no such possibility exists, I would suggest to add that in future version - cf. Breck's answer.

asked 14 Oct '10, 13:00

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 07 Feb '13, 11:54

Mark%20Culp's gravatar image

Mark Culp

Good one... until now I was happy with LIST(), I didn't know I wanted that, now I am all disappointed and dissatisfied :)

(14 Oct '10, 14:19) Breck Carter

...but I'm sure you would want a DBMS without LIST() even less, right?

(14 Oct '10, 14:51) Volker Barth

Indeed... LIST ranks up there with DEFAULT TIMESTAMP as features the other vendors just don't get let alone have.

(14 Oct '10, 15:04) Breck Carter

If I remember correctly, in version 7.0 there was no ORDER BY for LIST and a clever customer used DISTINCT for this purpose, recognizing that in 7.0 DISTINCT aggregates were only ever computed by sorting the input to the group-by node by the (single) DISTINCT aggregate's argument. Sadly this "neat trick" no longer works because the hash group-by method can now evaluate multiple distinct aggregates and it doesn't enforce an ordering on the argument. Some execution plans may order, others may not.

(29 Oct '10, 19:25) Ivan T. Bowman

I vote "yes" for an "ORDER BY SELF" enhancement, or ORDER BY 1, or simply ORDERED.

The examples in the Help, such as SELECT LIST( EmployeeID ORDER BY Surname ), simply do not exist in the real world... I cannot imagine ever needing to see '1013,191,750,921,868,1658,...'

Yes, I know what the Help is trying to show, and yes, I often use ORDER BY other-columns-not-in-the-list-expression, but in the simple cases ORDER BY SELF is what is needed: SELECT LIST( EmployeeID ORDER BY EmployeeID )

The Help DOES seem to imply that SELECT LIST( Street ) FROM Employees without an ORDER BY is implicitly ordered by Street: '487 Kennedy Court, 547 School Street' which is in fact just an accident.

permanent link

answered 14 Oct '10, 14:16

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

You could wrap the complex expression into a derived table.

A quick little example with the demo database:

SELECT list(emp_record ORDER BY emp_record) 
FROM (SELECT '(' || EmployeeID || ') ' || GivenName || ' ' || SurName as emp_record FROM Employees) AS a_table;

Edit: This could also be made into a function of its own...

CREATE OR REPLACE FUNCTION fn_list(expr VARCHAR(1000),delimiter VARCHAR(10), source
VARCHAR(255), sortorder VARCHAR(4))
    'SELECT list(expr' || 
        IF (delimiter <> '') THEN ', ''' || delimiter || '''' ELSE '' END IF || 
        IF (sortorder <> '') THEN ' ORDER BY expr ' || sortorder ELSE '' END IF || 
    ') into ret FROM (' || '
        SELECT ' || expr || ' as expr FROM ' || source || 
            IF (sortorder <> '') THEN ' ORDER BY expr ' || sortorder ELSE '' END IF || 
        ') as a_tab';

Resulting in a fairly friendly syntax:

SELECT fn_list('GivenName || '' '' || SurName',', ','Employees','ASC');

And depending on when you expect this list to change, you could make the function deterministic, so the result may end up being cached.

permanent link

answered 14 Oct '10, 16:09

Tyson%20Lewis's gravatar image

Tyson Lewis
accept rate: 22%

edited 29 Oct '10, 17:49

Thanks, yes, that's a workaround I could use (and a common table expression would be another alternative, I guess). But in my current situation, the SELECT LIST()... itself is used as a subquery-expression in the SELECT list of a complex query, and that seems to make it too complex to work (for my brain, at least). - So a simple LIST(col1 ORDER BY 1) would be wayyyy easier:)

(15 Oct '10, 09:00) Volker Barth

@Volker I tried to help make a shorthand in the edit above, perhaps that'll help. Sorry no documentation, just something I drafted up.

(29 Oct '10, 17:50) Tyson Lewis
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 14 Oct '10, 13:00

question was seen: 1,482 times

last updated: 07 Feb '13, 11:54