Results 1 to 6 of 6

Thread: Help nEeded!!

  1. #1
    Join Date
    Mar 2003
    Posts
    10

    Unanswered: 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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2003
    Posts
    10
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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".
    Last edited by blindman; 03-26-04 at 16:22.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •