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

    Unanswered: count(name) > 1 [MS SQL 2005]

    for MS SQL 2005

    I want to find all users with same sName

    CREATE TABLE [Users] (
    [id_Users] [int] NOT NULL PRIMARY KEY CLUSTERED ,
    [fName] [varchar] (100),
    [sName] [varchar] (100)
    )



    I must get all the rows with count(sName) > 1

    20 , Aldous Leonard, Huxley
    26 , Bob, Huxley

    .......

    thank you for helping

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink Re

    Code:
    SELECT     id_Users, fName, sName 
    FROM         Users
    WHERE     (sName IN
                              (SELECT     sName
                                FROM          Users
                                GROUP BY SName
                                HAVING      (COUNT(SName) > 1)))
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    marvels, typically we do not just hand students the answers to their homework questions, we try to teach them how to do it themselves, using helpful questions like "what have you tried so far?" and "why do you think that's not working correctly?"

    please try to keep up to our high standards of not making it easy for students to cheat

    thanks

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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    anselme is a student?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    thank you Marvels
    thanks to all

Posting Permissions

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