On Thu, Jan 30, 2003 at 12:13:04AM +0000, Paul Makepeace wrote:
> On Wed, Jan 29, 2003 at 12:10:38PM +0000, the hatter wrote:
> > On Wed, 29 Jan 2003, Paul Makepeace wrote:
> >
> > > So there are a bunch of things "order by rank". I'd like to implement a
> > > move up/down in SQL. So say the target was id=20 moving up, I'd like its
> > > rank to become 1, and id=10's rank to become 2.
> >
> > $query = "UPDATE fw SET rank=rank+(-2*(rank-($position+0.5))) WHERE ";
> > $query .= "(rank=$position OR rank=$position+1)"
>
> Oh my word. Rewritten with bound variables we have,
>
> UPDATE pageplans
> SET rank = rank - 2*rank + 2*:1 + 1
> WHERE rank=:1 OR rank=:1+1
OK, combining this and the thing I'd written before,
update pageplans
set rank = rank
+ (select min(rank) - :1 from pageplans where rank > :1)
- 2 * (rank - :1)
where rank = :1
or rank = (select min(rank) from pageplans where rank > :1)
d00d.
Depending on whether the subselects are recalculated after each update,
and if it hits the higher ranked one first, it could actually fail (I
think..). It was nicely flipping the two numbers in PG though. I might
brave the PG users list at some point.
Paul
--
Paul Makepeace ....................................... http://paulm.com/
"If beer were the colour of grass, then resistance is futile!"
-- http://paulm.com/toys/surrealism/
