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.....
LVL 5
theoaksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
so, something like this?

select col1, newidcol = row_number() order by idcol)
from tablename
group by col1
0
theoaksAuthor Commented:
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
0
Kevin CrossChief Technology OfficerCommented:
Just add an aggregate like min.
select col1, newidcol = row_number() over(order by min(idcol))
from tablename
group by col1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Kevin CrossChief Technology OfficerCommented:
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

Open in new window

0
SharathData EngineerCommented:
You can also try with dense_rank and distinct
select distinct col1,dense_rank() over(order by col1) as newidcol from Yourtable

Open in new window

0
theoaksAuthor Commented:
thanks guys both worked well!!

much appreciated
0
chapmandewCommented:
well poop....I missed out.
0
Kevin CrossChief Technology OfficerCommented:
@chapmandew, thought you would have been credited for original suggestion.  I was just helping get over the syntax issues.  :) Sorry about that.
0
chapmandewCommented:
No worries at all....Im just getting back into the swing of things...have been out for some time
0
theoaksAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.