| Author |
 Topic  |
|
|
kirank
Starting Member
USA
45 Posts |
 Posted - 08/19/2010 : 04:45:41
|
here is my table records input
Name id
a 1
a 2
c 3
and output will be
a 2/1
c 3
as per the above structure i want the final output which will be
a 2/1
c 3
here a is distinct so it will take only one a(any a with id) and c
i have try this but it not giving proper result.
select id from Mapt
where id not in (select DISTINCT(Name),id from Mapt)
if i try this it will give me wrong result but if i only make distinct on name then its working and if i add id then its not
finally i want the
a 1/2
c 3
after this result i want to delete a 1/2 result.
means if a 1 selected
then a 2 will be deleteed from table
hope u will get it
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
kirank
Starting Member
USA
45 Posts |
Posted - 08/19/2010 : 05:44:19
|
| no not getting , i want simple query is it possible |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/19/2010 : 06:40:33
|
It can't more simple than this-- Mimic your environment
DECLARE @Sample TABLE
(
Name CHAR(1) NOT NULL,
id TINYINT NOT NULL
)
INSERT @Sample
SELECT 'a', 1 UNION ALL
SELECT 'a', 2 UNION ALL
SELECT 'c', 3
-- Here is the solution
SELECT x.Name,
STUFF(y.Data, 1, 1, '') AS IDs
FROM (
SELECT DISTINCT
Name
FROM @Sample
) AS x
CROSS APPLY (
SELECT '/' + CAST(w.id AS VARCHAR(3))
FROM @Sample AS w
WHERE w.Name = x.Name
ORDER BY w.id DESC
FOR XML PATH('')
) AS y(Data)
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
kirank
Starting Member
USA
45 Posts |
Posted - 08/19/2010 : 07:00:39
|
thx for reply here the output is
name id
a 2/1
c 3
dear i want either 1 or 2 i dont want 2/1 or 1/2
here if i is selected then 2 records (with 2id) is deleted
and if i selected then another records get deleted
if there is N no of records then 1 should be there and rest all will be deleted
thank good stuffs |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/19/2010 : 07:15:45
|
SELECT Name, MIN(ID) FROM @Sample GROUP BY Name
N 56°04'39.26"
E 12°55'05.63" |
Edited by - SwePeso on 08/19/2010 07:19:03 |
|
|
|
vaibhavktiwari83
Aged Yak Warrior
India
837 Posts |
Posted - 08/19/2010 : 07:17:19
|
Still not clear your requirement. more explain the problem.
this your post
" i want the final output which will be
a 2/1
c 3 "
and now you are writing
"i want either 1 or 2 i dont want 2/1 or 1/2"
this is very confusing at all.
before understanding the solution of peso
i want to understand the problem.
Vaibhav T
To walk FAST walk ALONE
To walk FAR walk TOGETHER |
|
|
|
kirank
Starting Member
USA
45 Posts |
Posted - 08/19/2010 : 07:36:34
|
dear its simple
see base on name i want to make distinct
for ex
a 1
a 2
b 3
now here a is repeating twise or may be more than that now wht i want is make a distinct on name
so output will be
a
b
now reg on id you may select any number of id either select 1 or 2 .
i have try this
SELECT Name, MIN(ID) FROM @Sample GROUP BY Name
but here distinct is not there on name
plz help
|
|
|
|
vaibhavktiwari83
Aged Yak Warrior
India
837 Posts |
Posted - 08/19/2010 : 07:45:55
|
Ok I got the problem
but if you have been used 1 or 2 instead of 1/2
then there might not be any misunderstanding.
anyways
SELECT Name FROM @Sample GROUP BY Name
is nothing but
SELECT distinct Name FROM @Sample
So this is for sure that
you have mistaken something to analyze the original data.
Vaibhav T
To walk FAST walk ALONE
To walk FAR walk TOGETHER |
Edited by - vaibhavktiwari83 on 08/19/2010 07:47:22 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/19/2010 : 07:48:04
|
quote:
Originally posted by kirank
but here distinct is not there on name
Yes it is. Do you get duplicate name?
Or, since we are not able to fully understand your problem, maybe you can post some more and accurate sample data and also the expected output based on the new provided sample data?
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/19/2010 : 07:50:53
|
-- Mimic your environment
DECLARE @Sample TABLE
(
Name CHAR(1) NOT NULL,
id TINYINT NOT NULL
)
INSERT @Sample
SELECT 'a', 1 UNION ALL
SELECT 'a', 2 UNION ALL
SELECT 'c', 3
-- Here is the solution
SELECT Name,
MIN(ID) AS ID
FROM @Sample
GROUP BY Name
-- REPRINTED OUTPUT
Name ID
a 1
b 3
N 56°04'39.26"
E 12°55'05.63" |
|
|
| |
Topic |
|
how to distinct and sort the records
