October 10, 2007 at 5:55 am
Hi,
I m sarvesh,
i have a problem in sql. i have a table and i want fetch distinct records on two fields, then what should do me for it.
pls help.:)
October 10, 2007 at 5:59 am
Can you provide the DDL (definition) for your table. What fields does it contain? Which two fields are required to be "distinct" in your result set?
______________________________________________________________________
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 6:09 am
i have a table.
fields =
1) Id
2) name
3) EmailId
4) Age
5) Salary
now i want distinct recordsbased on name and EmailId then what should do me
October 10, 2007 at 6:13 am
SELECTID,
Name,
EmailID,
Age,
Salary
FROM(
SELECTID,
Name,
EmailID,
Age,
Salary,
COUNT(*) OVER (PARTITION BY Name, EmailID) AS RecID
FROMTable1
) AS d
WHERERecID = 1
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 6:24 am
Hey Peter ,
Just Check this,
I am getting the Output for only ID 3 , why so?
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','bbb','1@abc.com',20,1000
SELECT ID, tName, Email, Age, Salary
FROM( SELECT ID, tName, Email, Age, Salary,
COUNT(*) OVER (PARTITION BY tName, Email) AS RecID
FROM #Temp
) AS d
WHERE RecID = 1
Cheers!
Sandy.:)
--
October 10, 2007 at 6:24 am
there is Error on query analyzer by executing this query.:)
October 10, 2007 at 6:29 am
Sarvesh,
First... when you get an error, you need to post it so we can see what's going on.
Second... are you actually using SQL Server 2005?
--Jeff Moden
October 10, 2007 at 6:30 am
My dear,
below error is coming
Incorrect syntax near the keyword 'OVER' 🙂
October 10, 2007 at 6:32 am
No, i m using SQL SERVER 2000
October 10, 2007 at 6:33 am
If you ARE using SQL Server 2005, make sure COMPATIBILITY LEVEL is set to 90.
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 6:33 am
You want row_number() not count(*) ....
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','bbb','1@abc.com',20,1000
SELECT ID, tName, Email, Age, Salary, recID
FROM( SELECT ID, tName, Email, Age, Salary,
ROW_NUMBER() OVER (PARTITION BY tName, Email ORDER BY tname, email) AS RecID
FROM #Temp
) AS d
WHERE RecID = 1
DROP TABLE #temp
[EDIT] This solution only works in SQL 2005.. (this forum.. *wink wink*) [/EDIT]
______________________________________________________________________
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 6:36 am
Sarvesh, I am sure you are using SQL Server 2000 as your database. The syntax is not supported in SQL Server 2000.
Prasad Bhogadi
www.inforaise.com
October 10, 2007 at 6:43 am
g.sarvesh (10/10/2007)
No, i m using SQL SERVER 2000
You are posting your questions to the 2005 TSQL forum. Any answers are likely to contain syntax that won't work in 2000. That's exactly what's happening here. Note the little bar at the top of the screen that says "SQL Server 2005." for the forum. Scroll down a little on the forums page and you'll find the 2005 forum where people will provide syntax specific to your version. Just in case, here's the link 2000/7 TSQL
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 10, 2007 at 7:14 am
Jason Selburg (10/10/2007)
You want row_number() not count(*) ....
Why?
ALL groups of records always start with 1, both distinct and non-distinct groups...
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 7:18 am
No, the count(*) will give you the count of records in that group, not an incremental row number....
Run your script and you'll see... *grin* :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 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply