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,
sudheerThis is how you get the records corresponding to the minimum IDs:

select Common.*
from Common
inner join
(select PartnerId, min(NativeID) NativeID
from Common) MinIDs
on Common.PartnerId = MinIDs.PartnerId
and Common.NativeID = MinIDs.NativeID

As far as getting the "TOP 1" of the duplicates, you need to specify a sort order, such as by Last Name.|||Originally posted by blindman
This is how you get the records corresponding to the minimum IDs:

select Common.*
from Common
inner join
(select PartnerId, min(NativeID) NativeID
from Common) MinIDs
on Common.PartnerId = MinIDs.PartnerId
and Common.NativeID = MinIDs.NativeID

As far as getting the "TOP 1" of the duplicates, you need to specify a sort order, such as by Last Name.

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|||Again, "TOP 1" is meaningless without specifying a sort order.

Here is some code that will select the between LastName in alphabetic order when there are duplicate NativeIDs:

select Common.*
from Common
inner join
(select PartnerID,
NativeID,
min(LastName) LastName
from Common
inner join
(select PartnerId, min(NativeID) NativeID
from Common
group by PartnerID) MinIDs
on Common.PartnerId = MinIDs.PartnerId
and Common.NativeID = MinIDs.NativeID
group by PartnerID, NativeID) MinRecords
on Common.PartnerID = MinRecords.PartnerID
and Common.NativeID = MinRecords.NativeID
and Common.LastName = MinRecords.LastName|||Originally posted by blindman
Again, "TOP 1" is meaningless without specifying a sort order. Nah, not quite meaningless. Without a unique ORDER BY, the TOP 1 syntax basically means "pick one random row from this set". I'd say that was practically useless, but not meaningless!

-PatP|||It's meaningless as a phrase when you are trying to pick the "top 1" of each of several groups. The TOP clause cannot even be used for this (efficiently).

I guess I should have specified "top 1" instead of "TOP 1". :)