Post reply

how to avoid rownumbers for duplicates

  • June 24, 2009 at 4:02 am

    #95604

    Hi,

    We have a list of tables which have 1..m relations. when we tried pagination to get the range of records based on ther order specified by the user

    example is:

    Select MD_Metadata.MetadataID FROM (SELECT ROW_NUMBER() OVER ( order by CI_Citation.Title asc,MD_Keyword.Keyword asc )

    AS row_number, MD_Metadata.MetadataID, MD_Metadata.FileIdentifier FROM ( MD_Metadata LEFT JOIN MD_Identification ON MD_Identification.MetadataID = MD_Metadata.MetadataID LEFT JOIN

    CI_Citation ON CI_Citation.CitationID = MD_Identification.CitationID LEFT JOIN

    CI_AlternateTitle ON CI_AlternateTitle.CitationID = CI_Citation.CitationID LEFT JOIN

    CI_Date ON CI_Citation.CitationID = CI_Date.CitationID LEFT JOIN

    MD_KeywordType ON MD_Identification.IdentificationID = MD_KeywordType.IdentificationID LEft JOIN

    MD_Keyword ON MD_KeywordType.KeywordTypeID = MD_Keyword.KeywordTypeID LEFT JOIN

    MD_Format ON MD_Metadata.MetadataID = MD_Format.MetadataID LEFT JOIN

    EX_Extent ON MD_Identification.IdentificationID = EX_Extent.IdentificationID LEFT JOIN

    GeographicDescription ON EX_Extent.ExtentID = GeographicDescription.ExtentID LEFT JOIN

    GeographicBoundingBox ON EX_Extent.ExtentID = GeographicBoundingBox.ExtentID )

    where ((MD_Metadata.MetadataID IN (11842,11845,11846))) )

    MD_Metadata WHERE MD_Metadata.row_number >= 1 AND MD_Metadata.row_number < =3
    though i have given the IDs only 11842,11845,11846 it has resulted me somany 162 row numbers due to the multiple values in the 1..m related tables which are joined for order purpose.

    So my consern is how to remove the duplicates and also the unique
    row numbers providing the order specified by the user.

    Any one can please help in resolving this????? 🙁

  • June 24, 2009 at 4:07 am

    #1014509

    Please provide table structure, sample data and expected result as describe in the link in my signature.

    From what you've described I don't know whether a GROUP BY would solve the problem or not.

    Like I said: I'd like to see sample data.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 24, 2009 at 2:19 pm

    #1014927

    Sounds like either the group by function (like Lutz mentioned), or the use of the windows ranking functions will do the job. And, like Lutz mentioned, we need more information to be able to help you out. Please see the link in my signature for how to provide data in a readily consumable format that we can use to help you with your issue.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 24, 2009 at 10:18 pm

    #1015073

    Use group by and max or min for each field to get unique record for each ID... not exactly sure what the question is though.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply