CODEWITHSUNDEEP
sqlt-sqlcommon-table-expressionranksql-update
user1179071
user1179071

Reputation: 77

How can I set a new column in a table based on the rank of other columns?

I have a Microsoft SQL Server table that has two columns in it - one for Locations and one for associated Images. Up until now, multiple images could be associated with the same location and only the last uploaded image was actually used by on the mobile site. This was achieved by assigning an ImageRank to each row:

RANK() OVER (PARTITION BY Location ORDER BY Image DESC) ImageRank

so that only rows with ImageRank = 1 were picked up by an SSIS process which dealt with the mobile Site:

Now we want to be able to pick which of the existing images (not just the last one) to display. For that purpose we introduced a third column - MobileSelection of type bit. Since this column was introduced late it has Nulls for all of the existing rows.

So I have something like this:

+-------+----------+-----------------+-----------+
| image | location | MobileSelection | ImageRank |
+-------+----------+-----------------+-----------+
|   850 |       10 |         NULL    |         1 |
|   209 |       10 |         NULL    |         2 |
+-------+----------+-----------------+-----------+

And I want to set only the row with ImageRank of 1 to 1. Keep in mind that ImageRank is not an actual column otherwise this would be trivial.

My goal is to be able to write an update statement which would assign a value of 1 to this column if the row it is on has an calculated ImageRank of 1. The issue I am running into is being able to combine the Rank Over Partition (or something similar) clause with the Update statement:

UPDATE dining_location SET MobileSelection = 1 WHERE RANK() OVER (PARTITION BY Location ORDER BY Image DESC) = 1;

The above returns this error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

I would appreciate any help with this. Thank you!

Upvotes: 0

Views: 1414

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use an updatable CTE:

WITH toupdate as (
      SELECT dl.*, RANK() OVER (PARTITION BY Location ORDER BY Image DESC) as seqnum
      FROM dining_location dl
     )
UPDATE toupdate
    SET MobileSelection = 1
    WHERE seqnum = 1;

Upvotes: 1

Shantanu Kher
Shantanu Kher

Reputation: 1054

Your requirement is to update mobileselection = 1 for latest images of each location.

You can use below SQL -

UPDATE dining_location SET MobileSelection = 1 
WHERE image in (select t.image from 
(select location, image, RANK() OVER (PARTITION BY Location ORDER BY Image DESC) as rrank from dining_location) t where t.rrank = 1);

Upvotes: 1

Related Questions