one id per reference

[shaan1980]

for a given table how can I generate one id per reference column

for example

X Y
a 2
aa 3
a 4
aaa 5
a 6

for every distinct value of X I want one value of Y

How do we express this in SQL?

[Rebrandt]

In your example X value "a" has 3 values of Y... (and you said you wanted new value of Y for a *DISTINCT* value of X) .. explain please?

[Romeo]

Could it help defining Y like
Y INT NOT NULL AUTO_INCREMENT
if you need a new Y for the next X being inserted.

Regards,
R.

[edited by: Romeo at 10:25 am (utc) on Dec. 29, 2005]

[aspdaddy]

for every distinct value of X I want one value of Y

Simply make column X a unique constraint or Primary Key

[shaan1980]

i don't have any permissions to change the table structure. think of this query as a 'example ' query for every distinct value of x I need a example record from Y

X Y
a 2
aa 3
a 4
aaa 5
a 6

for this example I want to retrieve

X Y
a 2 (can be 4 or 6 doesn't matter)
aa 3
aaa 5

[shaan1980]

Can this be done on excel?

[aspdaddy]

select x, max(y) from table group by x

[shaan1980]

would this work if y was alpha neumeric?

[shaan1980]

strike my previous request ... this works perfectly for me

Thank you sir