October 10, 2007 at 7:27 am
Op wrote "Now I want distinct records based on name and EmailId..."
If you want all DISTINCT records, use COUNT and RecID = 1
If you want all NON-DISTINCT records, use COUNT and RecID > 1
If you use ROW_NUMBER() and RecID = 1, you will get first record for ALL groups.
If you use ROW_NUMBER() and RecID > 1, you will get all but first record for ALL groups.
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 7:46 am
Peter, forgive me here (bold is only being used for clarity, not yelling). But ...
given the table ....
CREATE TABLE #temp
(Id int,
tname varchar(10),
Email varchar(10),
Age int,
Salary int)
INSERT INTO #temp
SELECT '1','aaa','1@abc.com',20,1000
UNION
SELECT '2','aaa','1@abc.com',20,1000
UNION
SELECT '3','aaa','2@abc.com',20,1000
UNION
SELECT '4','bbb','1@abc.com',20,1000
UNION
SELECT '5','bbb','1@abc.com',20,1000
UNION
SELECT '6','bbb','2@abc.com',20,1000
UNION
SELECT '7','bbb','2@abc.com',20,1000
UNION
SELECT '8','bbb','3@abc.com',20,1000
Wouldn't the expected results be ...
ID tName Email Age Salary recID
1 aaa 1@abc.com20 1000 1
3 aaa 2@abc.com20 1000 1
4 bbb 1@abc.com20 1000 1
6 bbb 2@abc.com20 1000 1
8 bbb 3@abc.com20 1000 1
If you use count(*) and = 1 you'll get ...
ID tName Email Age Salary recID
3 aaa 2@abc.com20 1000 1
8 bbb 3@abc.com20 1000 1
...just let me know if I'm still looking at this wrong...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 10, 2007 at 7:55 am
I think {1, 3, 4, 6, 8} is not the DISTINCT records.
They are one from each combination of Name and EmailID.
Maybe that is what OP really want?
Records {3, 8} are the DISTINCT records because there exists no duplicates.
That's what I interpret OP request.
Maybe OP ultemately want records {1, 2, 4, 5, 6, 7} ??
Because they are the only records with duplicates?
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 7:58 am
Isn't that what DISTINCT means?
If you have a set of 1,1,1,2,3,3,3,4 are the DISTINCT numbers not 1,2,3, and 4?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 10, 2007 at 8:03 am
You may be right.
We'll never know until OP gets back.
Other times when I encountered requests for "DISTINCT" posted by people living in India, they have meant non-duplicates.
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 8:05 am
OK, well then I may not be going nuts.... :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply