Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql distrinct query

Posted on 2009-07-14
10
Medium Priority
?
302 Views
Last Modified: 2012-05-07
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.....
0
Comment
Question by:theoaks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24855763
so, something like this?

select col1, newidcol = row_number() order by idcol)
from tablename
group by col1
0
 
LVL 5

Author Comment

by:theoaks
ID: 24855812
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1600 total points
ID: 24855825
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24855846
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 24855942
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
 
LVL 5

Author Comment

by:theoaks
ID: 24856034
thanks guys both worked well!!

much appreciated
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24856055
well poop....I missed out.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24856085
@chapmandew, thought you would have been credited for original suggestion.  I was just helping get over the syntax issues.  :) Sorry about that.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24856093
No worries at all....Im just getting back into the swing of things...have been out for some time
0
 
LVL 5

Author Comment

by:theoaks
ID: 24856658
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question