compare Fields of the same table

[atiqraza]

Could someone please tell me how i can compare a field with the proceeding record field within the same table, I have a table in which i want to compare the field Cusomter# with the customer # in the next record of the same table. Can anybody please help me with this.
Thanks

for example

NAme Customer #
abc 111
abd 123

I would like to create a table which would allow me to compare the customer# of the two record....

 

[JarlH]

You have to find a way to read the "proceeding" row. (You know that the table row order is actually undefined. Generally the records are stored in primary key order. But I wouldn't build applications depending on the pk order...)

SELECT t.name,
t.customer,
(SELECT customer FROM tablename
WHERE name = (SELECT MAX(name) FROM tablename
WHERE name < t.name))
FROM tablename AS t
ORDER BY name

 

[atiqraza]

Hey thnx this does give me an idea but my table looks like this

IND No Cust_NO Flag
0 2 0
0 2 10
0 2 11
0 2 12
0 2 14

What i want to do is populate the flag field and put a 1 when the Cust_no field is different then the proceeding one. When it is the same i want it to put a 0. Any idea how i can do that ?

 

[atiqraza]

Can we use if statements to create new colums in sql, suppose i want to copy a whole table, and insert a new colums which says
if country = Canada insert 'CA'
anybody know the syntax for that ?

 

[JarlH]

Do not create another table storing all the data once again. Waste of memory and adds complexity to your system since data has to updated twice when necessary.

You could add a column to your existing table:

ALTER TABLE countrytable ADD countrycode CHAR(2);
UPDATE countrytable SET countrycode = 'CA'
WHERE country = 'Canada';

Or, even better, create a new table to store country-codes.
CREATE TABLE countrycodes
(countrycode char(2) primary key,
countryname varchar(50) not null unique);

Then create a view that returns the wanted result.

CREATE VIEW countryview AS
SELECT ct.*, cc.countrycode
FROM countrytable AS ct, countrycodes AS cc
WHERE ct.countryname = cc.countryname;

(Actually, I think it would be even even better to store countrycode in your original table, and the country names in this extra help table...)