Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Documentation says:</p> <blockquote> <p>DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. [...] Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. [...] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).</p> </blockquote> <p><a href="http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT" rel="noreferrer">Official documentation</a></p> <p>So you'll have to add the <code>address_id</code> to the order by.</p> <p>Alternatively, if you're looking for the full row that contains the most recent purchased product for each <code>address_id</code> and that result sorted by <code>purchased_at</code> then you're trying to solve a greatest N per group problem which can be solved by the following approaches:</p> <p>The general solution that should work in most DBMSs:</p> <pre><code>SELECT t1.* FROM purchases t1 JOIN ( SELECT address_id, max(purchased_at) max_purchased_at FROM purchases WHERE product_id = 1 GROUP BY address_id ) t2 ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at ORDER BY t1.purchased_at DESC </code></pre> <p>A more PostgreSQL-oriented solution based on @hkf's answer:</p> <pre><code>SELECT * FROM ( SELECT DISTINCT ON (address_id) * FROM purchases WHERE product_id = 1 ORDER BY address_id, purchased_at DESC ) t ORDER BY purchased_at DESC </code></pre> <p>Problem clarified, extended and solved here: <a href="https://stackoverflow.com/questions/9796078">Selecting rows ordered by some column and distinct on another</a></p>
    singulars
    1. This table or related slice is empty.
    1. POPostgreSQL DISTINCT ON with different ORDER BY
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMosty Mostacho
    1. USMosty Mostacho
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPostgreSQL DISTINCT ON with different ORDER BY
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt works, but gives wrong ordering. That's why i want to get rid of address_id in order clause
      singulars
      1. PO
      1. USsl_bug
    2. CODocumentation is clear: You can't because the selected row will be unpredictable
      singulars
      1. PO
      1. USMosty Mostacho
    3. COBut may be there is another way to select latest purchases for disticnt addresses?
      singulars
      1. PO
      1. USsl_bug
 

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