Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: SELECT field > 1 time

    hello !

    for MS SQL 2000

    how can I get something like

    SELECT name from users WHERE Count(name) > 1

    i want to return only the rows where name appears more than 1 time

    thank you

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    select name
    from users
    group by name
    having count(*) > 1
    Regards
    Kris Zywczyk

  3. #3
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    select name
    from users
    group by name
    having count(*) >= 1

    OR

    select distinct name
    from users
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rajeshpatel
    OR

    select distinct name
    from users
    no -- this does not meet the required conmdition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by r937
    no -- this does not meet the required conmdition
    Yeah, I misinterpret the requirement...

    Actually he wants the name list which are more than one time in the database.

    This will work...
    Code:
    originally by Kris Zywczyk 
    
    select name
    from users
    group by name
    having count(*) > 1
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  6. #6
    Join Date
    Dec 2005
    Posts
    266
    select name
    from users
    group by name
    having count(*) > 1

    works fine !

    thank you

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by r937
    no -- this does not meet the required conmdition
    This should:

    Code:
    select distinct name from (select name from users minus select distinct name from users)
    ... but I'll bet it would return an empty set.
    Last edited by sco08y; 12-16-06 at 15:46.

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    to get all of them and not only one i am using

    select name from users
    WHERE name IN(
    select name
    from users
    group by name
    having count(*) > 1
    )


    is it the best way ?
    Last edited by anselme; 12-17-06 at 22:44.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is almost the best way

    however, you are returning only the name

    so you will get results like this --

    fred
    fred
    todd
    todd
    todd
    biff
    biff

    i think you should have additional columns in the outer SELECT clause, to help you understand which rows are involved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2005
    Posts
    266
    yes it was just an exemple

    I am using

    select name, sport, town,any from users
    WHERE name IN(
    select name
    from users
    group by name
    having count(*) > 1
    )


    thank you r937

  11. #11
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by anselme
    he best way ?
    There is no best way, ever. There are only tradeoffs and priorities. Code can be maintainable, correct or fast. You can rarely have all three. But you often don't *need* all three.

    That code is definitely maintainable and correct. As to whether it's fast, ensure that it even *matters* whether it's fast by profiling.

  12. #12
    Join Date
    Dec 2005
    Posts
    266
    thank you sco08y

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by sco08y
    There is no best way, ever. There are only tradeoffs and priorities. Code can be maintainable, correct or fast. You can rarely have all three. But you often don't *need* all three.
    That really stands out as a quote. I think i will use that as a signature line
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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