SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to distinct and sort the records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kirank
Yak Posting Veteran

USA
58 Posts

Posted - 08/19/2010 :  04:45:41  Show Profile  Visit kirank's Homepage  Reply with Quote

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
22744 Posts

Posted - 08/19/2010 :  05:00:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kirank
Yak Posting Veteran

USA
58 Posts

Posted - 08/19/2010 :  05:44:19  Show Profile  Visit kirank's Homepage  Reply with Quote
no not getting , i want simple query is it possible
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 08/19/2010 :  06:40:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

kirank
Yak Posting Veteran

USA
58 Posts

Posted - 08/19/2010 :  07:00:39  Show Profile  Visit kirank's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 08/19/2010 :  07:15:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 08/19/2010 :  07:17:19  Show Profile  Reply with Quote
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
Go to Top of Page

kirank
Yak Posting Veteran

USA
58 Posts

Posted - 08/19/2010 :  07:36:34  Show Profile  Visit kirank's Homepage  Reply with Quote
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
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 08/19/2010 :  07:45:55  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 08/19/2010 :  07:48:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 08/19/2010 :  07:50:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000