Rick said:
What on earth does this have to do withthe questione that was asked? The
user wants to increment a field based on another field as he demonstrated in
the example.
So do you agree this "DESP NO" is a calculated column? But how do we
know how to rank the "DESP NO" against each occurrence of country name?
One solution would be this
CREATE TABLE Countries (
ID COUNTER NOT NULL,
COUNTRY TEXT(30) NOT NULL,
CONSTRAINT pk__Countries PRIMARY KEY (ID));
INSERT INTO Countries (COUNTRY) VALUES ('UK')
INSERT INTO Countries (COUNTRY) VALUES ('GREECE');
INSERT INTO Countries (COUNTRY) VALUES ('GREECE');
INSERT INTO Countries (COUNTRY) VALUES ('ITALY');
INSERT INTO Countries (COUNTRY) VALUES ('UK');
SELECT T1.COUNTRY, (
SELECT COUNT(*)
FROM Countries
WHERE T1.COUNTRY = COUNTRY
AND ID <= T1.ID
) AS [DESP NO]
FROM Countries AS T1;
The nature of the autonumber (COUNTER) column give us the order.
Non-maintained clustered index, monotonic integer, exposed physical
locator - call it what you like but what we are talking about is
date/time order it was entered into the system and has nothing to do
with the data model. As soon as the system becomes part of the model
you know you have problems e.g. what if a row was added to the system
'late'?
In my experience there is something in the model to give such order and
it is usually a data element of DATETIME type. For example
CREATE TABLE Orders (
order_nbr TEXT(10) NOT NULL,
dispatch_date DATETIME NOT NULL,
dispatch_country TEXT(30) NOT NULL,
CONSTRAINT pk__Orders PRIMARY KEY (order_nbr));
INSERT INTO Orders VALUES
('4548181814', #2005-07-21#, 'UK');
INSERT INTO Orders VALUES
('7055727553', #2005-07-22#, 'GREECE');
INSERT INTO Orders VALUES
('2300007860', #2005-07-20#, 'GREECE');
INSERT INTO Orders VALUES
('9377223113', #2005-07-20#, 'UK');
SELECT T1.dispatch_country, (
SELECT COUNT(*)
FROM Orders
WHERE T1.dispatch_country = dispatch_country
AND dispatch_date <= T1.dispatch_date
) AS [DESP NO]
FROM Orders AS T1;
