how to distinct and sort the records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to distinct and sort the records

Author

Topic

kirank
Yak Posting Veteran

58 Posts

Posted - 2010-08-19 : 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

22864 Posts

Posted - 2010-08-19 : 05:00:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

kirank
Yak Posting Veteran

58 Posts

Posted - 2010-08-19 : 05:44:19

no not getting , i want simple query is it possible

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 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
Yak Posting Veteran

58 Posts

Posted - 2010-08-19 : 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

30421 Posts

Posted - 2010-08-19 : 07:15:45

SELECT Name, MIN(ID) FROM @Sample GROUP BY Name

N 56°04'39.26"
E 12°55'05.63"

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-19 : 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
Yak Posting Veteran

58 Posts

Posted - 2010-08-19 : 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

843 Posts

Posted - 2010-08-19 : 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 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

30421 Posts

Posted - 2010-08-19 : 07:50:53

[code]-- 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[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources