Re: Renumber a field which a order using UPDATE
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 01/14/05
- Next message: David Portas: "Re: How can I insert records to a new table from a old table?"
- Previous message: jyz: "How can I insert records to a new table from a old table?"
- In reply to: Joel Leong: "Renumber a field which a order using UPDATE"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 14 Jan 2005 08:52:36 -0000
You can do this:
UPDATE Fruits
SET rank =
(SELECT COUNT(*)
FROM Fruits AS F
WHERE F.rank < Fruits.rank
OR (F.rank = Fruits.rank
AND F.fruit <= Fruits.fruit))
However, it's probably better not to store the rank at all, otherwise you
have to update all the ranks whenever the table changes. You can instead
calculate the Rank in a query:
SELECT F1.fruit, COUNT(*) AS rank
FROM Fruits AS F1
JOIN Fruits AS F2
ON F1.rank > F2.rank
OR (F1.rank = F2.rank
AND F1.fruit >= F2.fruit)
GROUP BY F1.fruit
-- David Portas SQL Server MVP --
- Next message: David Portas: "Re: How can I insert records to a new table from a old table?"
- Previous message: jyz: "How can I insert records to a new table from a old table?"
- In reply to: Joel Leong: "Renumber a field which a order using UPDATE"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
