Re: Need help with reordering items

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 07/08/04 

Date: Thu, 08 Jul 2004 08:45:15 -0700

It really helps if you post DDL. Let me make assumptions about the keys
and constraints. Also, the name "order_id" does not tell me what this
column does, so I changed it to "sort_pos " instead. It sounded like we
were taking orders in a retail situation. I am also going to drop the
"country_id" to make the basic algorithm easier to see. But look up the
ISO country codes and USPS specs
for the real thing.

CREATE TABLE Cities
(city VARCHAR (20) NOT NULL PRIMARY KEY,
 sort_pos INTEGER NOT NULL UNIQUE);

INSERT INTO Cities VALUES ('New York', 1);
INSERT INTO Cities VALUES ('Chicago', 2);
INSERT INTO Cities VALUES ('Washington', 3);
INSERT INTO Cities VALUES ('Denver', 4);
INSERT INTO Cities VALUES ('Atlanta', 5);
INSERT INTO Cities VALUES ('Austin', 6);
 
>> I was thinking about stored procedure and loop thru but there must be
a better way. <<

The mere fact that you said "loop" means you are a complete newbie. You
now have a chance to learn to avoid proprietary syntax when Standard SQL
is available. As an exercise, look at all the answers you get and circle
all of the needlessly proprietary code you are given.

The real trick is to start to think in sets, and not procedural steps.
Analysis of the problem: this is an UPDATE on one table; no rows are
added or removed and no data has to come back to us. Ergo, we need only
one update statement.

Let's look for known **facts**, instead of thinking of a **procedure**.
Draw pictures to help you think; a number line or a deck of cards is
good for this problem.

1) When I move a position (old, new) then any position outside that
range does not change.

2) There is a special "do nothing" case where (old = new)

3) A position can move up or down, depending on the direction of the
Move; that is, (old < new) or (old > new)

So we have three possible updates: up, down and stay put. That implies
a CASE expresion. In pseudo-code, think a pattern like this:

UPDATE Cities SET position = CASE <<up, down, stay put>> ;

The CASE expression tests the WHEN clauses in the order they were
written, so you can get the advantage of short-circuited evaluation. I
am assuming that most of the rows are not going to change, so we do that
first.

CREATE PROCEDURE SwapCities
(@old_pos INTEGER, @new_pos INTEGER)
AS
UPDATE Cities
    SET sort_pos
        = CASE
          WHEN @old_pos = @new_pos
          THEN sort_pos
          -- old < new position
          WHEN sort_pos BETWEEN @old_pos AND @new_pos
          THEN CASE WHEN sort_pos = @new_pos THEN sort_pos - 1
                    WHEN sort_pos = @old_pos THEN @new_pos
                    ELSE sort_pos - 1 END

          -- old > new position
          WHEN sort_pos BETWEEN @new_pos AND @old_pos
          THEN CASE WHEN sort_pos = @new_pos THEN sort_pos + 1
                    WHEN sort_pos = @old_pos THEN @new_pos
                    ELSE sort_pos + 1 END
          ELSE sort_pos END;

Inside the range, a row is either the new value, the old value or
something else. Try a concrete example of moving a city down the list:
MoveCities (3, 5). Positions 1, 2 and 6 stay where they are; 3
(Washington) becomes 5; 4 becomes 3 (Denver) and 5 becomes 4 (Atlanta)

New York 1
Chicago 2
Washington 3
Denver 4
Atlanta 5
Austin 6

Becomes:

New York 1
Chicago 2
Denver 3
Atlanta 4
Washington 5
Austin 6

Try a concrete example of moving a city up the list: MoveCities (5, 3).
Positions 1, 2 and 6 stay where they are; 5 (Atlanta) becomes 3; 4
(Denver) becomes 5 and 3 Washington) becomes 4

New York 1
Chicago 2
Washington 3
Denver 4
Atlanta 5
Austin 6

Becomes:

New York 1
Chicago 2
Atlanta 3
Washington 4
Denver 5
Austin 6

The final ELSE clause is a programming trick to assure that you do not
get unwanted nulls; it says leave things alone.

The first WHEN clause! Without it, the (old = new) causes a key
violation. Technically, that gives you the right answer (i.e. stay
put), but errors look ugly. Exercise for the student: why does the
violation occur?

Notice this solution had no local variables, no IF or WHILE constructs,
no proprietary syntax. It is one statement. It takes at least a year
of full-time SQL programming to finally throw away all that procedural
baggage, but keep working at it.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: been running a day camp all week...
    ... say, London, where I grew up? ... New York is worse for traffic, ... the roads and it's not a lot better round here at times. ... big cities are big cities and a different matter entirely. ...
    (uk.people.support.depression)
  • Re: F.Y.I. - Revised paper "P=NP: LP Formulation of the TSP"
    ... > cities, ... > constraints still dominates the number of possible paths. ... between Problem IP and Problem IP-bar in my paper is that *extreme ... polytope compared to the number of paths. ...
    (comp.theory)
  • Re: Violent Crime Up for 1st Time in 5 Years
    ... Despite the national numbers, Detroit, Los Angeles and New York were ... among several large cities that saw the number of murders drop. ... violent crime rates (with murder serving as a proxy for violent crime ... Las Vegas, ...
    (alt.true-crime)
  • Re: The worlds best cities for quality of life
    ... New York, conducts a survey of the world's leading cities to determine ... better quality of life than their counterparts in the US. ...
    (rec.gambling.poker)
  • Re: The worlds best cities for quality of life
    ... New York, conducts a survey of the world's leading cities to determine ... better quality of life than their counterparts in the US. ...
    (rec.gambling.poker)