sql distrinct query
hi all,
can someone tell me how i can do this:
lets say i have a table: (idcol being a primary key)
idcol col1
1 200
2 200
3 300
4 400
5 900
6 3000
i want a query that will distinct just the second field (col1) column so return will look like:
Id col1
1 200
2 300
3 400
4 900
5 3000
the id in the return table DOES NOT need to be value pulled from the original idcol (assuming it probably cant without being part of an aggregate function anyways). im happy if we build a id from say ROW_NUMBER
someone help please.....
can someone tell me how i can do this:
lets say i have a table: (idcol being a primary key)
idcol col1
1 200
2 200
3 300
4 400
5 900
6 3000
i want a query that will distinct just the second field (col1) column so return will look like:
Id col1
1 200
2 300
3 400
4 900
5 3000
the id in the return table DOES NOT need to be value pulled from the original idcol (assuming it probably cant without being part of an aggregate function anyways). im happy if we build a id from say ROW_NUMBER
someone help please.....
ASKER
threw an error expectiong "over" so i did:
select col1, newidcol = row_number() over(order by idcol)
from tablename
group by col1
and now saying that idcol is not contained in an aggregate function
select col1, newidcol = row_number() over(order by idcol)
from tablename
group by col1
and now saying that idcol is not contained in an aggregate function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This also works if the newid corresponds to the lowest to highest values in col1.
select col1, newidcol = row_number() over(order by col1)
from t
group by col1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks guys both worked well!!
much appreciated
much appreciated
well poop....I missed out.
@chapmandew, thought you would have been credited for original suggestion. I was just helping get over the syntax issues. :) Sorry about that.
No worries at all....Im just getting back into the swing of things...have been out for some time
ASKER
sorry chapmandew, but my original efforts where already pretty close to correct, and i need an exact answer as i wasnt that far off myself ( i just missed the aggregate function in the over clause)...
thanks anyway
thanks anyway
select col1, newidcol = row_number() order by idcol)
from tablename
group by col1