Help Needed !!
-
Hi,
I have a table structure like this
TableName: Common
Columns
PartnerId: int
NativeId: int
FirstName: nvarchar(50)
LastName:nvarchar(50)
1)I should get the records with a minimum native id for a particular PartnerId,
2) if duplicates exists in the above condition i should select top 1 (first record)
How can i do it??
TIA,
sudheer
Regards,
Sudheer
-
What about
SELECT TOP 1 MIN(nativeid), FirstName, LastName
FROM Common
WHERE PArtnerID = 'ID'
GROUP BY FirstName, LastName
-
Hi,
thnx for that but i have other prob with other table
my data is like this
PartnerId NativeId FirstName
2000 45 Sudheer
2000 45 SUdheer1
3000 46 Mytest
3000 46 Mytest1
4000 47 Mytest2
4000 47 Mytest3
.... and it goes on
i shuld pick top 1 record of each of the partner ids and my result set shuldbe
PartnerId NativeId FirstName
2000 45 Sudheer
3000 46 Mytest
4000 47 Mytest2
TIA,
sudheer
Regards,
Sudheer -
If you do not have constraints using stored procedures then you can try this.
ALTER PROCEDURE TOPROWS
AS
DECLARE @NATIVEID INT
CREATE TABLE #COMMON
(TPARTNERID INT,
TNATIVEID INT,
TFIRSTNAME NVARCHAR(100),
LASTNAME NVARCHAR(100))
DECLARE DNATIVEID CURSOR FOR
SELECT DISTINCT(NATIVEID)
FROM
COMMON
OPEN DNATIVEID
FETCH NEXT FROM DNATIVEID INTO @NATIVEID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #COMMON(TPARTNERID,TNATIVEID,TFIRSTNAME,LASTNAME)
SELECT TOP 1 partnerid,nativeid,FIRSTNAME,LASTNAME FROM COMMON WHERE NATIVEID=@NATIVEID
FETCH NEXT FROM DNATIVEID INTO @NATIVEID
END
CLOSE DNATIVEID
DEALLOCATE DNATIVEID
SELECT * FROM #COMMON
-----------------------------------------------------------------------------------------
EXEC TOPROWS will give you the desired out put
Thanks
Prasad Bhogadi
www.inforaise.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply