June 7, 2005 at 5:09 am
Hello firends!!!
I have question!
for example lets consider one table where i have three fields like Id , Name ,Country
I am using Sql server2000 and i want out put like
Id FirstName Country
10 John A
20 Mack
30 Rock
40 Silk B
Mann C
I just explain @ my output suppose if any above column there is duplicate values coming then that should be group by only one like 'A' or like '40'. Only one output should display
Is that possible?
Please Help Me Out
Thanks In Advance
Shashank
Regards,
Papillon
June 7, 2005 at 6:15 am
Can you show us the raw data in the table?
June 7, 2005 at 6:20 am
Hello Remi!
These all rows are only data in my table just i want to ignore duplicate rows rather i mean to say ignore only duplicate values from my output!
Thanks In Advance
Shashank
Regards,
Papillon
June 7, 2005 at 6:26 am
I don't want to seem needy, but I don't see any duplicates in the data you sent us.
Which columns have duplicate values?
June 7, 2005 at 6:36 am
Hello,
My table structure like this
Id FirstName Country
10 John A
20 Mack A
30 Rock A
40 Silk B
40 Mann C
Please Help Me Out!!!!!
Shashank
Regards,
Papillon
June 7, 2005 at 6:48 am
Why are you trying to remove the duplicate ids and country?
Are you sure you need this in a single statement?
June 7, 2005 at 7:01 am
Hello,,
Actually this is very small data i was showing but in application we have very big data and client donnot want duplicates value to display like that we facing problem i am not sure that it will finish in single statement
is there any idea do u have to solve problem??
shashank
Regards,
Papillon
June 7, 2005 at 7:23 am
There is no duplicate data in this list :
10 John A
20 Mack A
30 Rock A
40 Silk B
40 Mann C
I see that the id 40 is repeating, and I frankly don't understand why it is. Also I would expect to have more than one customer cmoing from the same country, so that is normal too.
Duplicate data as I understand it would be this :
10 John A
10 John A
20 Mack A
30 Rock A
30 Rock A
30 Rock A
Now you have 6 rows where there should have only 3.
Is this query for a report operation or to clean the data in the table?
June 7, 2005 at 9:49 pm
Hello Remi,
I want each value only once in report
like as shown in my first forum..
if any body knows this please help me out
thanks in advance its urgent
shashank
Regards,
Papillon
June 8, 2005 at 2:42 am
what if Mack is Id 30 and country D? how would you like the output to look then?
June 8, 2005 at 6:38 am
I think that this task would be much easier to handle at the client side. You could use group by on the report to remove some of the doubles, then yo could manually remove the rest or the doubles.
No that it's impossible to do in TSQL, but this is really a task for the client.
June 8, 2005 at 7:15 am
If you have a KEY on your table you can achieve what you want otherwise it won't work (like here where IdRow is not a Key...).
set nocount on
create table #t(IdRow int,FirstName varchar(20),Country char(1))
INSERT INTO #t VALUES (10,'John','A')
INSERT INTO #t VALUES (10,'John1','A')
INSERT INTO #t VALUES (20,'Mack','A')
INSERT INTO #t VALUES (30,'Rock','A')
INSERT INTO #t VALUES (40,'Silk','B')
INSERT INTO #t VALUES (40,'Mann','C')
Select * from #t
DECLARE @MyTable TABLE(IdRow int, Country char(1))
INSERT INTO @MyTable
SELECT MIN(IDRow),Country
FROM #t
GROUP BY Country
SELECT * FROM @MyTable
SELECT A.IDRow, A.FirstName, CASE WHEN B.IDRow IS NULL THEN '' ELSE A.Country END As Country
FROM #t A LEFT OUTER JOIN @MyTable B
ON A.IDRow=B.IDRow AND A.Country=B.Country
Drop table #t
Vasc
June 8, 2005 at 7:28 am
This query returns :
10 John A
10 John1 A
20 Mack
30 Rock
40 Silk B
40 Mann C
if I understand his requirements he wants this (without the __) :
10 John A
__ John1 __
20 Mack
30 Rock
__ Silk B
40 Mann C
June 8, 2005 at 7:34 am
The data you have does not have a true duplicate. As mentioned before, there are duplicate ID's. You're unique key would be ID and First Name.
SELECT ID,FirstName,Country FROM @table GROUP BY ID,FirstName
This will give you unique columns. You could also do this:
SELECT DISTINCT * FROM @table
Another option, if you never want to return a row where there is a duplicate ID value, you could do this:
SELECT ID,FirstName,Country,COUNT(ID) AS NumIDs FROM @table GROUP BY ID,FirstName HAVING COUNT(ID) = 1
Without more information I cannot give you any additional suggestions. One thing that would drive other options is the information you are asking the user to input. If you are simply selecting everything and displaying it in a grid, I would use one of the queries above.
From a design perspective, ID should never be duplicated. If ID is a surrogate key to another table, I would relabel it, if possible and add a new field to act as your true ID column.
June 8, 2005 at 9:22 am
At least (ID,Country) has to be key for the Solution to work.
Otherwise one more step need to be added to treat this rows depending on FirstName...
set nocount on
create table #t(IdRow int,FirstName varchar(20),Country char(1))
INSERT INTO #t VALUES (10,'John','A')
INSERT INTO #t VALUES (10,'John1','A')
INSERT INTO #t VALUES (20,'Mack','A')
INSERT INTO #t VALUES (30,'Rock','A')
INSERT INTO #t VALUES (40,'Silk','B')
INSERT INTO #t VALUES (40,'Mann','C')
Select * from #t
DECLARE @MyTable TABLE(IdRow int, Country char(1))
INSERT INTO @MyTable
SELECT MIN(IDRow),Country
FROM #t
GROUP BY Country
DECLARE @MyTable1 TABLE(IdRow int, Country char(1), Cnt int)
INSERT INTO @MyTable1
SELECT A.IDRow,A.Country,CASE WHEN B.IDRow IS NULL THEN 0 ELSE 1 END
FROM
@MyTable A
LEFT OUTER JOIN
(SELECT IDRow,MIN(Country) as Country
FROM @MyTable
GROUP BY IDRow) B
ON A.IDRow=B.IDRow and A.Country=B.Country
SELECT * FROM @MyTable1
SELECT CASE WHEN Cnt=1 THEN ltrim(str(A.IDRow)) ELSE '' END AS IDRow, A.FirstName, CASE WHEN B.IDRow IS NULL THEN '' ELSE A.Country END As Country
FROM #t A LEFT OUTER JOIN @MyTable1 B
ON A.IDRow=B.IDRow AND A.Country=B.Country
Drop table #t
Vasc
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply