Results 1 to 13 of 13

Thread: Unique Records

  1. #1
    Join Date
    Apr 2004
    Posts
    73

    Unanswered: Unique Records

    Is there a way to get a Stored Procedure to return only unique records from a table? I am using a Stored PRocedure to query a table and it returns all the records in the table and there are many duplicates in the information. I am using SQL Server 2K.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select distinct foo, bar, etc from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    94
    select distinct ... ?

    darn pipped at the post! :-))

  4. #4
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Yes Select Distinct like this.

    SELECT DISTINCT * FROM YourTable


    this will return unique records

  5. #5
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Thumbs up Unique records

    That will do it everytime

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it will, except if the table does not have a primary key

    with a primary key, SELECT DISTINCT * will always return all rows in the table

    but then, so will SELECT *

    therefore SELECT DISTINCT * is unnecessary

    also, SELECT DISTINCT * is very expensive, as it requires the entire table be sorted on all columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    yes, it will, except if the table does not have a primary key
    I suspect that you meant if the table has a surrogate key, such as a SQL Server IDENTITY column.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are just foolin with my head, and it won't work

    whether the primary key is natural or surrogate makes no difference

    if the table has no primary key, then there could be a difference between SELECT * and SELECT DISTINCT *

    with one, they are the same
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Only because the primary key makes each record unique.

    You could have the same column of unique values, but not designate it as a primary key, and the results would be the same.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    indeed, that is precisely what a primary key is for

    notice how both you and i used the word "could"

    with a primary key, they will be the same, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. I agree with everyone about everything.

    Splitting hairs gives me a splitting headache.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    "distinct" will provide you the unique records for the fields you request. If you use Select Distinct * from table, and you have a primary key in your table, you will always get all records returned.

    However, if you use Select Distinct Field1, Field2, Field3... from table (where Field1, Field2, Field3 are the fields of information you want in your answer) then you will get a list of unique records.

  13. #13
    Join Date
    Oct 2003
    Posts
    706
    I agree. DISTINCT is the "correct" way to specify that the query-result should contain no duplicates. And, as a rule of thumb, you should always be sure that your query specifies what you want, not how you think the DBMS should produce it! SQL is not a "procedural" language.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.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
  •