StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is a simplistic approach. You might find <code>merge</code> to be better. These versions preserve the highest col1 value and modify the maxdate column. Aaron's preserves the row with the maxdate. That is a distinction that I doubt is important but should be noted.</p> <pre><code>update MyTable set col4 = ( select max(col4) from MyTable as m2 where m2.col2 = MyTable.col2 and m2.col3 = MyTable.col3 ), col5 = ( select count(*) from MyTable as m2 where m2.col2 = MyTable.col2 and m2.col3 = MyTable.col3 ) where not exists ( select * from MyTable as m2 where m2.col2 = MyTable.col2 and m2.col3 = MyTable.col3 and m2.col1 > MyTable.col1 and m2.col4 > MyTable.col4 or m2.col4 = MyTable.col4 and m2.col1 > MyTable.col1 ); delete from MyTable where exists ( select * from MyTable as m2 where m2.col2 = MyTable.col2 and m2.col3 = MyTable.col3 and m2.col1 > MyTable.col1 ); </code></pre> <p><strong>EDIT 2</strong> Here's my shot at a <code>merge</code> query</p> <pre><code>merge MyTable as target using ( select max(col1), col2, col3, max(col4), count(*) from Mytable group by col2, col3 ) as source(id, col2, col3, maxdate, rowcount) on ( target.col1 = source.col1 and target.col2 = target.col2 and target.col3 = target.col3 ) when matched then update set col4 = maxdate, col5 = rowcount when not matched then delete </code></pre> <p><strong>EDIT 3</strong> Preserve the row with the original maxdate, breaking ties on col1</p> <pre><code>-- option #1 update MyTable set col5 = ( select count(*) from MyTable as m2 where m2.col2 = MyTable.col2 and m2.col3 = MyTable.col3 ) where not exists ( select * from MyTable as m2 where m2.col2 = MyTable.col2 and m2.col3 = MyTable.col3 and m2.col4 > MyTable.col4 or m2.col4 = MyTable.col4 and m2.col1 > MyTable.col1 ); delete from MyTable where exists ( select * from MyTable as m2 where m2.col2 = MyTable.col2 and m2.col3 = MyTable.col3 and m2.col4 > MyTable.col4 or m2.col4 = MyTable.col4 and m2.col1 > MyTable.col1 ); -- option #2 merge MyTable as target using ( select max(col1), col2, col3, max(col4), count(*) from Mytable group by col2, col3 ) as source(maxid, col2, col3, maxdate, rowcount) on ( target.col2 = target.col2 and target.col3 = target.col3 and target.col1 = maxid and target.col4 = maxdate ) when matched then update set col5 = rowcount when not matched then delete </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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