| Author |
Topic |
|
Vaishu
Posting Yak Master
178 Posts |
 Posted - 2008-01-18 : 11:50:43
|
| HiI have a table with col1,col2,....upto col30Col1 may have duplicate string like below Col1-----------------Col2202-345567-456677 Robert202-345567-456677 Robert020-456789-892543 PhilHow do I select DISTINCT ROW from the table?SELECT DISTINCT COL1,COl2..... FROM tbl - Seems not working. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-18 : 11:53:20
|
| What do you want for the other columns?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-18 : 11:54:33
|
| If you are only concerned about unique values of Col1 and not others columns then use:-SELECT Col1,MAX(Col2),MAX(Col3),...FROM TableGROUP BY Col1 |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2008-01-21 : 17:27:38
|
HiMost of the time I get duplicate ROW (Orders from the customer)and colums of that rowa are identical. So I need to get only distinct row from the table. How do I do this?quote:
Originally posted by visakh16
If you are only concerned about unique values of Col1 and not others columns then use:-SELECT Col1,MAX(Col2),MAX(Col3),...FROM TableGROUP BY Col1
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-01-22 : 00:35:19
|
quote:
Originally posted by Vaishu
HiMost of the time I get duplicate ROW (Orders from the customer)and colums of that rowa are identical. So I need to get only distinct row from the table. How do I do this?quote:
Originally posted by visakh16
If you are only concerned about unique values of Col1 and not others columns then use:-SELECT Col1,MAX(Col2),MAX(Col3),...FROM TableGROUP BY Col1
Can you post the expected result?MadhivananFailing to plan is Planning to fail |
|
|
|
sundaram_r_1984
Starting Member
11 Posts |
Posted - 2008-01-22 : 02:13:55
|
| col1 spouse kid spouse_salary----- ------ ---- ---------------robet priy ram 100000robet riya rahem 300000jay nena ralf 200000ravi sen manu 3500you are saying that you want a single row distinct "robet" record. if you see the above table, there may be dublicate records for "robet" in the table. But the information in each of the "robet" record are different from the others. so if you only need the "col1" column to be displayed without any duplicate records, the query is simple."select distinct col1 from <tablename>"but u cannot get a distinct row for the "col1 = robet" since the other column values are totally different. but if you want the total salary for each member's spouse in the table without duplication then it is possible with this query."select col1, sum(spouse_salary) from <tablename> group by col1"the output would be like thiscol1 spouse_salary----- -------------robet 400000 (100000 + 300000 since robet got two wife so he gets two spouse salaries)jay 200000ravi 3500so what are you looking for... reply |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2008-01-22 : 04:37:59
|
| Hicol1 spouse kid spouse_salary, Add1,add2,add3,postcode,----- ------ ---- ---------------robet priy --ram-- 100000 ----102-some st--city--N18 34Rrobet riya rahem 300000-- ----102-some st--city--N18 34Rjay nena ralf 200000ravi sen manu 3500From the above scenarioI am doing the select command to insert in to table(Employee) the Salary details will go to table(Salary_detail). So the expected result isrobet ----102-some st--city--N18 34R |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-22 : 07:17:25
|
quote:
Originally posted by Vaishu
Hicol1 spouse kid spouse_salary, Add1,add2,add3,postcode,----- ------ ---- ---------------robet priy --ram-- 100000 ----102-some st--city--N18 34Rrobet riya rahem 300000-- ----102-some st--city--N18 34Rjay nena ralf 200000ravi sen manu 3500From the above scenarioI am doing the select command to insert in to table(Employee) the Salary details will go to table(Salary_detail). So the expected result isrobet ----102-some st--city--N18 34R
You can get this by select distinct col1,add1,add2,add3,postcode from tableas you are ignoring all column values |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2008-01-23 : 10:26:37
|
HiCan you please look at this below posting. Need help ?http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96027quote:
Originally posted by madhivanan
quote:
Originally posted by Vaishu
HiMost of the time I get duplicate ROW (Orders from the customer)and colums of that rowa are identical. So I need to get only distinct row from the table. How do I do this?quote:
Originally posted by visakh16
If you are only concerned about unique values of Col1 and not others columns then use:-SELECT Col1,MAX(Col2),MAX(Col3),...FROM TableGROUP BY Col1
Can you post the expected result?MadhivananFailing to plan is Planning to fail
|
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2008-01-23 : 10:30:35
|
Hi It doesn't work. I am using the query like below.select distinct '1' + Right(col1,7),add1 as x1,add2 AS X2,add3,postcode from table.Any idea ??quote:
Originally posted by visakh16
quote:
Originally posted by Vaishu
Hicol1 spouse kid spouse_salary, Add1,add2,add3,postcode,----- ------ ---- ---------------robet priy --ram-- 100000 ----102-some st--city--N18 34Rrobet riya rahem 300000-- ----102-some st--city--N18 34Rjay nena ralf 200000ravi sen manu 3500From the above scenarioI am doing the select command to insert in to table(Employee) the Salary details will go to table(Salary_detail). So the expected result isrobet ----102-some st--city--N18 34R
You can get this by select distinct col1,add1,add2,add3,postcode from tableas you are ignoring all column values
|
|
|
|
|
How to select distinct row from a table ?