| Author |
Topic |
|
masterdineen
Aged Yak Warrior
550 Posts |
 Posted - 2011-12-19 : 08:35:41
|
| HelloI have a query that returns a list of users and there company and email address.But some have a multiple email addresses and company_names.Each user has a ref number, so some appear more than once.How do i select them distinctly returning all columns.data example.552 rob davis552 rob davis222 lisa smith665 martin bassett665 martin bassettI know i can select distinct just by selecting the ref number, but i want to return all columns.MCTS / MCITP certified |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-19 : 08:40:46
|
| [code];with cte as( select *, row_number() over (partition by ref_number order by (select null)) as RN from employees)select * from cte where rn=1;[/code]This will get you one (randomly selected) row for each employee. If you want it to be more sophisticated - for example, get all e-mail addresses, more work will need to be done. |
 |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2011-12-19 : 08:56:59
|
| no that is excellent. i have my list now,thank you very much |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-19 : 12:17:34
|
| very welcome. |
|
|
|
Nikhil341
Starting Member
9 Posts |
Posted - 2011-12-19 : 16:54:54
|
| Hey, I have a similar type of question.If "CTE" is a table name in the above explanation, is there any generic SQL statement (Not including the Table name) to produce same kind of O/P?Please have a look on the "Removing multiple rows in Excel by using the SQL." issue which was posted by me.Thanks.Thanks. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2011-12-20 : 05:11:15
|
| Yes really.sometimes forget the basics, we are not all perfect american rebots |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-12-20 : 07:03:43
|
| Those answer your question certainly.However, why does your schema allow this?If you have a more normalised structure then this problem wouldn't exist.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
selecting distinct with with all columns
ReallyBrett8-)Hint: Want your questions answered fast? Follow the direction in this link