Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Lets look at this two ways, i'll use postgres 9.0 as my reference database</p> <p>(1)</p> <pre><code>-- select rows from foo select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id -- where we don't have any rows from T2 having not exists ( -- select rows from foo select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id -- where the average score for any row is greater than the average for -- any row in T1 having avg2 &gt; avg1); id | avg1 -----+-------------------- 106 | 4.5000000000000000 (1 row) </code></pre> <p>then let's move some of the logic inside the subquery, getting rid of the 'not' : (2)</p> <pre><code>-- select rows from foo select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id -- where we do have rows from T2 having exists ( -- select rows from foo select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id -- where the average score is less than or equal than the average for any row in T1 having avg2 &lt;= avg1); -- I think this expression will be true for all rows as we are in effect doing a --cartesian join -- with the 'having' only we don't display the cartesian row set id | avg1 -----+-------------------- 106 | 4.5000000000000000 107 | 4.0000000000000000 (2 rows) </code></pre> <p>so you have got to ask yourself -- what do you actually mean when you do this correlated subquery inside a having clause, if it evaluates every row against every row from the primary query we are making a cartesian join and I don't think we should be pointing fingers at the SQL engine.</p> <p>if you want every row that is less than the maximum average What you should be saying is:</p> <pre><code>select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id having avg1 not in (select max(avg1) from (select id,avg(score) avg1 from foo group by id)) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhy does SELECT results differ between mysql and sqlite?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USPaddy Carroll
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThat select statement in my question is really not that difficult to understand; I simply want the correct result ;-)
      singulars
      1. PO
      1. This table or related slice is empty.
    2. COI contend that it is ambiguous and will provide an indeterminate result across different technologies
      singulars
      1. PO
      1. USPaddy Carroll
    3. COIt seems it's not ambiguous to MySql, MS Sql, and now Postgres; and it also seems that the main developer of sqlite is making changes in reaction to the email I sent to sqlite-users@sqlite.org.
      singulars
      1. PO
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload