Getting back to SQL magic
Recently I was asked for a way of numbering a subset of records of database table. It could be done using a programming language, as I did with a small code in Visual Basic for Applications using MS Access connected through ODBC to the database server.
But I wanted the real deal, so several ideas came to my mind. The first one was to transfer the desired rows to a temporary table, while deleting them from the original one to later create a new column of type serial that will handle the numbering. However, this idea was not possible as different set of rows required to have their own counter. Let's say that different cities required to have their sales numbered using independent counters.
The solution I used was to create a temporary sequence value, to then update the table for each city value. Something like this in Postgresql:
ALTER TABLE sales ADD COLUMN number;
CREATE TEMP SEQUENCE counter;
UPDATE TABLE sales set number=n FROM (SELECT nextval('counter') AS n, order FROM sales WHERE city='desired_city' ORDER BY date) AS foo WHERE sales.order=foo.order;
DROP SEQUENCE counter;
But I wanted the real deal, so several ideas came to my mind. The first one was to transfer the desired rows to a temporary table, while deleting them from the original one to later create a new column of type serial that will handle the numbering. However, this idea was not possible as different set of rows required to have their own counter. Let's say that different cities required to have their sales numbered using independent counters.
The solution I used was to create a temporary sequence value, to then update the table for each city value. Something like this in Postgresql:
ALTER TABLE sales ADD COLUMN number;
CREATE TEMP SEQUENCE counter;
UPDATE TABLE sales set number=n FROM (SELECT nextval('counter') AS n, order FROM sales WHERE city='desired_city' ORDER BY date) AS foo WHERE sales.order=foo.order;
DROP SEQUENCE counter;
Comments