<p>I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.</p>

<p>So I'm thinking:</p>

<pre><code>UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)
</code></pre>

<p>But my brain hurts going any farther than that.</p>

[{'answer_id': 54430, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 10, 'selected': True, 'text': "<pre><code>SELECT DISTINCT a,b,c FROM t\n</code></pre>\n\n<p>is <em>roughly</em> equivalent to:  </p>\n\n<pre><code>SELECT a,b,c FROM t GROUP BY a,b,c\n</code></pre>\n\n<p>It's a good idea to get used to the GROUP BY syntax, as it's more powerful.  </p>\n\n<p>For your query, I'd do it like this:</p>\n\n<pre><code>UPDATE sales\nSET status='ACTIVE'\nWHERE id IN\n(\n    SELECT id\n    FROM sales S\n    INNER JOIN\n    (\n        SELECT saleprice, saledate\n        FROM sales\n        GROUP BY saleprice, saledate\n        HAVING COUNT(*) = 1 \n    ) T\n    ON S.saleprice=T.saleprice AND s.saledate=T.saledate\n )\n</code></pre>\n"}, {'answer_id': 54557, 'author': 'Christian Berg', 'author_id': 5035, 'author_profile': 'https://Stackoverflow.com/users/5035', 'pm_score': 5, 'selected': False, 'text': "<p>The problem with your query is that when using a GROUP BY clause (which you essentially do by using distinct) you can only use columns that you group by or aggregate functions. You cannot use the column id because there are potentially different values. In your case there is always only one value because of the HAVING clause, but most RDBMS are not smart enough to recognize that.</p>\n\n<p>This should work however (and doesn't need a join):</p>\n\n<pre><code>UPDATE sales\nSET status='ACTIVE'\nWHERE id IN (\n  SELECT MIN(id) FROM sales\n  GROUP BY saleprice, saledate\n  HAVING COUNT(id) = 1\n)\n</code></pre>\n\n<p>You could also use MAX or AVG instead of MIN, it is only important to use a function that returns the value of the column if there is only one matching row.</p>\n"}, {'answer_id': 12632129, 'author': 'Erwin Brandstetter', 'author_id': 939860, 'author_profile': 'https://Stackoverflow.com/users/939860', 'pm_score': 9, 'selected': False, 'text': '<p>If you put together the answers so far, clean up and improve, you would arrive at this superior query:</p>\n<pre class="lang-sql prettyprint-override"><code>UPDATE sales\nSET    status = \'ACTIVE\'\nWHERE  (saleprice, saledate) IN (\n    SELECT saleprice, saledate\n    FROM   sales\n    GROUP  BY saleprice, saledate\n    HAVING count(*) = 1 \n    );\n</code></pre>\n<p>Which is <em>much</em> faster than either of them. Nukes the performance of the currently accepted answer by factor 10 - 15 (in my tests on PostgreSQL 8.4 and 9.1).</p>\n<p>But this is still far from optimal. Use a <a href="https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS" rel="noreferrer"><strong><code>NOT EXISTS</code></strong></a> (anti-)semi-join for even better performance. <code>EXISTS</code> is standard SQL, has been around forever (at least since PostgreSQL 7.2, long before this question was asked) and fits the presented requirements perfectly:</p>\n<pre class="lang-sql prettyprint-override"><code>UPDATE sales s\nSET    status = \'ACTIVE\'\nWHERE  NOT EXISTS (\n   SELECT FROM sales s1                     -- SELECT list can be empty for EXISTS\n   WHERE  s.saleprice = s1.saleprice\n   AND    s.saledate  = s1.saledate\n   AND    s.id &lt;&gt; s1.id                     -- except for row itself\n   )\nAND    s.status IS DISTINCT FROM \'ACTIVE\';  -- avoid empty updates. see below\n</code></pre>\n<p><em>db&lt;&gt;fiddle <a href="https://dbfiddle.uk/?rdbms=postgres_11&amp;fiddle=26c7eb96c3a22330a9c271d554c869fe" rel="noreferrer">here</a></em><br />\n<sub>Old <a href="http://sqlfiddle.com/#!17/6b5ef/1" rel="noreferrer">sqlfiddle</a></sub></p>\n<h3>Unique key to identify row</h3>\n<p>If you don\'t have a primary or unique key for the table (<code>id</code> in the example), you can substitute with the system column <code>ctid</code> for the purpose of this query (but not for some other purposes):</p>\n<pre><code>   AND    s1.ctid &lt;&gt; s.ctid\n</code></pre>\n<p><sub>Every table should have a primary key. Add one if you didn\'t have one, yet. I suggest a <code>serial</code> or an <code>IDENTITY</code> column in Postgres 10+.</sub></p>\n<p>Related:</p>\n<ul>\n<li><a href="https://stackoverflow.com/questions/17500013/in-order-sequence-generation/17503095#17503095">In-order sequence generation</a></li>\n<li><a href="https://stackoverflow.com/questions/9875223/auto-increment-table-column/9875517#9875517">Auto increment table column</a></li>\n</ul>\n<h3>How is this faster?</h3>\n<p>The subquery in the <code>EXISTS</code> anti-semi-join can stop evaluating as soon as the first dupe is found (no point in looking further). For a base table with few duplicates this is only mildly more efficient. With lots of duplicates this becomes <em>way</em> more efficient.</p>\n<h3>Exclude empty updates</h3>\n<p>For rows that already have <code>status = \'ACTIVE\'</code> this update would not change anything, but still insert a new row version at full cost (minor exceptions apply). Normally, you do not want this. Add another <code>WHERE</code> condition like demonstrated above to avoid this and make it even faster:</p>\n<p>If <code>status</code> is defined <code>NOT NULL</code>, you can simplify to:</p>\n<pre><code>AND status &lt;&gt; \'ACTIVE\';\n</code></pre>\n<p>The data type of the column must support the <code>&lt;&gt;</code> operator. Some types like <code>json</code> don\'t. See:</p>\n<ul>\n<li><a href="https://stackoverflow.com/questions/24292575/how-to-query-a-json-column-for-empty-objects/24296054#24296054">How to query a json column for empty objects?</a></li>\n</ul>\n<h3>Subtle difference in NULL handling</h3>\n<p>This query (unlike the <a href="https://stackoverflow.com/a/54430/939860">currently accepted answer by Joel</a>) does not treat NULL values as equal. The following two rows for <code>(saleprice, saledate)</code> would qualify as &quot;distinct&quot; (though looking identical to the human eye):</p>\n<pre><code>(123, NULL)\n(123, NULL)\n</code></pre>\n<p>Also passes in a unique index and almost anywhere else, since NULL values do not compare equal according to the SQL standard. See:</p>\n<ul>\n<li><a href="https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns/8289253#8289253">Create unique constraint with null columns</a></li>\n</ul>\n<p>OTOH, <code>GROUP BY</code>, <code>DISTINCT</code> or <code>DISTINCT ON ()</code> treat NULL values as equal. Use an appropriate query style depending on what you want to achieve. You can still use this faster query with <a href="https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE" rel="noreferrer"><code>IS NOT DISTINCT FROM</code></a> instead of <code>=</code> for any or all comparisons to make NULL compare equal. More:</p>\n<ul>\n<li><a href="https://stackoverflow.com/questions/26769454/how-to-delete-duplicate-rows-without-unique-identifier/26773018#26773018">How to delete duplicate rows without unique identifier</a></li>\n</ul>\n<p>If all columns being compared are defined <code>NOT NULL</code>, there is no room for disagreement.</p>\n'}, {'answer_id': 48238006, 'author': 'frans eilering', 'author_id': 4962958, 'author_profile': 'https://Stackoverflow.com/users/4962958', 'pm_score': 2, 'selected': False, 'text': "<p>I want to select the distinct values from one column 'GrondOfLucht' but they should be sorted in the order as given in the column 'sortering'. I cannot get the distinct values of just one column using</p>\n\n<pre><code>Select distinct GrondOfLucht,sortering\nfrom CorWijzeVanAanleg\norder by sortering\n</code></pre>\n\n<p>It will also give the column 'sortering' and because 'GrondOfLucht' AND 'sortering' is not unique, the result will be ALL rows.</p>\n\n<p>use the GROUP to select the records of 'GrondOfLucht' in the order given by 'sortering</p>\n\n<pre><code>SELECT        GrondOfLucht\nFROM            dbo.CorWijzeVanAanleg\nGROUP BY GrondOfLucht, sortering\nORDER BY MIN(sortering)\n</code></pre>\n"}, {'answer_id': 54456645, 'author': 'Abdulhafeth Sartawi', 'author_id': 4385453, 'author_profile': 'https://Stackoverflow.com/users/4385453', 'pm_score': 3, 'selected': False, 'text': "<p>If your DBMS doesn't support distinct with multiple columns like this:</p>\n\n<pre><code>select distinct(col1, col2) from table\n</code></pre>\n\n<p>Multi select in general can be executed safely as follows:</p>\n\n<pre><code>select distinct * from (select col1, col2 from table ) as x\n</code></pre>\n\n<p>As this can work on most of the DBMS and this is expected to be faster than group by solution as you are avoiding the grouping functionality.</p>\n"}]
['https://Stackoverflow.com/questions/54418', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4915/']