DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL Query Syntax

  1. #1
    Join Date
    Feb 2001
    Posts
    17

    Question

    Hi,

    I have a problem that might actually be quite trivial; however, I do not find a solution to it. Here it is:

    You have the following table TAB

    ID | Name |...
    ----------------------
    1 | Mouse |
    2 | Duck |
    3 | Mouse |
    4 | Goose |
    ....

    I want to select all rows where the name is the identical; i.e. in this example, I want to select row 1 and 3. Does anyone know the correct syntax for this statement? (I tried to solve it by using a view, but that wasn't the right track....)

    -Thanks a lot!
    Dan

  2. #2
    Join Date
    Oct 2000
    Posts
    90
    I don't know if this helps but one command you might want to think about is HAVING. So if you do


    SELECT name
    FROM table
    GROUP BY name
    HAVING COUNT(*) > 1

    This would display all names where there is more than one record in the table with the same name.


  3. #3
    Join Date
    Jun 2001
    Location
    Sri Lanka
    Posts
    4
    select * from TAB
    where name in(select name from TAB group by name having count(1) > 1)

  4. #4
    Join Date
    Feb 2001
    Posts
    17

    Smile

    Hi,

    that's exactly what I've been looking for! Thanks a lot for the hint!

    Dan

  5. #5
    Join Date
    Feb 2001
    Posts
    125
    Hi

    Try this


    Select * from table_name
    where name in (select name
    from table_name
    group by name
    having count(name) >= 2)


    Thanks
    P Soni

    [Edited by PSoni on 06-12-2001 at 08:26 AM]

Posting Permissions

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


Click Here to Expand Forum to Full Width