Re: Default order on displaying data without order clause

I think you're confusing my point.

You're right that you never want to add an *implicit* default to the order
clause. That would be changing the SQL standard which, as you've noted,
would be really bad.

But, we're talking about a tool that constructs *explicit* queries in order
to display the data in the table... I.e., when you right-click on a table
in pgAdmin and select "View/Edit Data...", pgAdmin is fully in control of
whether it generates either

SELECT * FROM public.sometable

or

SELECT * FROM public.sometable ORDER BY somecolumn ASC

In the second case, there is no *implicit* default... it's very explicitly
specified, and does not in anyway change/contradict the SQL standard. It's
just the tool stepping in and doing what I end up having to do manually.
every. frickin'. time.

Another UI concept that could be applied here is to simply remember my
previous settings, on a table-by-table basis. E.g., if I do a "View/Edit
Data..." once, and *explicitly* change the ORDER BY clause to be "ORDER BY
price DESC" then it's very a very smart thing to add to SELECT the next
time I do a "View/Edit Data..." on that table. And if I subsequently
change to be "ORDER BY qty ASC" then that becomes a smart default to
continue to use on subsequent "View/Edit Data..." queries. Or, if I
actually remove the default ORDER BY, then it's smart thing to remember
that choice and continue to use no ORDER BY.

But certainly, if the user explicitly indicates that they *do* want an
ORDER BY clause, then continuously defaulting to no ORDER BY clause each
time they do a "View/Edit Data...", on the same table, is arbitrarily
picking a behaviour that you clearly know that the user doesn't prefer.
Why not just do for the user the same thing they explicitly asked you to do
for last time? From a UX perspective, this approach requires the least
amount of interaction by the user in order to get what exactly they want.

And from a UX design perspective, the advantage of the this approach is
that it doesn't require yet-another option to support/document.

Cheers,
Dave

On Fri, Mar 15, 2019 at 1:59 PM Doug Easterbrook <doug(at)artsman(dot)com> wrote:

> I’m disagreeing with dave.
>
> to be clear. you NEVER want to add an implicit default to the order
> clause. EVER.
>
> Postgres returns data in the order that it returns it (sql standard)
> unless you specify otherwise.
>
> the python mantra: it is better to be EXPLICIT than IMPLICIT.
>
> If you do not provide an explicit sort order and rely on some implicit
> ordering, then your code will break in the future if you are relying on the
> order from the engine… and its harder to find problems if pgadmin has
> different behaviour than sending the same query through psql or the
> postgres libraries.
>
> final point is that PGadmin just gives the database your query. if
> Pgadmin altered my SQL code for me, I’d be angry since i’ve wanted to know
> what order postgres felt it wanted to return the data.
>
> its not pgadmin's job to fix my mistakes. it my job to fix my mistakes.
>
>
> do I agree that people want a natural order in a gui. Absolutely but
> you need to be explicit which one.
>
>
>
> *Doug Easterbrook*
> *Arts Management Systems Ltd.*
> mailto:doug(at)artsman(dot)com <doug(at)artsman(dot)com>
> http://www.artsman.com
> Phone (403) 650-1978
>
> On Mar 15, 2019, at 8:24 AM, Dave Caughey <caugheyd(at)gmail(dot)com> wrote:
>
> Yeah, but that's just what the spec says about how the SELECT works. It's
> not a statement about how a user interface should display information.
>
> Presenting unordered information is basically guaranteed to be never what
> the user wants. In this case, the user has to *almost always* edit the
> query to add a sensible "order by" clause.
>
> On the other hand, picking a default column (e.g., PK, or insertion order)
> is probably going to be the desirable behaviour most of the time (but
> rarely, not). In this case, the user has *almost never* edit the query
> to remove/alter the default "order by" clause.
>
> So you've got a UI decision of doing something that'll be "right" (i.e.,
> what the user wants) most of the time, or "wrong" (i.e., what the user
> doesn't want) most of the time. This is an easy win.
>
> Cheers,
> Dave
>
>
> On Fri, Mar 15, 2019 at 9:19 AM Wim Bertels <wim(dot)bertels(at)ucll(dot)be> wrote:
>
>> Janus schreef op vr 15-03-2019 om 12:20 [+0100]:
>> > In pgAdmin 3, an implicit default order by primary key is used when
>> > displaying data without an otherwise stated order. This is not the
>> > case in pgAdmin 4. The order seems somewhat arbitrary and the
>> > following statement from this site is definitely not true: "When you
>> > query data from a table, PostgreSQL returns the rows in the order
>> > that they were inserted into the table."
>>
>> Hallo Janus,
>>
>> this standard behaviour, as described by the ISO standard
>> :: ie unless you specify an ORDER BY clause, the order can be anything,
>> like a (unordered) set;
>>
>> the example of "the way there were inserted", this might often be the
>> case, but it's not a rule
>> more background: see planner, shared mem and cache
>>
>> >
>> > I do understand that some order of course exists, but from a
>> > human perspective it would make sense that default ordering is by PK,
>> > unless stated otherwise. In PgAdmin 4, is it possible to set a
>> > default ordering, at least so that displaying a table's contents
>> > using the "View Data" button, i.e. the query being run is altered
>> > from "SELECT oid, * FROM public.mytable" to "SELECT oid, * FROM
>> > public.mytable ORDER BY <primary key(s)>"?
>>
>> i only see the filter/sort option
>>
>> >
>> > Thanks!
>> --
>> mvg,
>> Wim Bertels
>> --
>> Lector
>> UC Leuven-Limburg
>> --
>> Let me take you a button-hole lower.
>> -- William Shakespeare, "Love's Labour's Lost"
>>
>>
>