Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: Opposite of Distinct

    I want to have ID of all the same record but i do not know why the result contains only one record. '3'

    ID|KOD|KRAJ
    1 601 D
    2 502 D
    3 601 D
    4 601 D

    SELECT ID FROM FVPOZ WHERE ID IN (SELECT COUNT(*) FROM FVPOZ WHERE FVNAG=1 GROUP BY KOD, KRAJ HAVING COUNT(*) > 1);

    How to get the ID value of the same records?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    I want to have ID of all the same record but i do not know why the result contains only one record. '3'
    the reason you're getting only one result is because you're trying to match an ID value to a COUNT() value

    here's what you want...
    Code:
    SELECT fvpoz.id
         , fvpoz.kod
         , fvpoz.kraj
      FROM ( SELECT kod
                  , kraj
               FROM fvpoz 
              WHERE fvnag=1 
             GROUP 
                 BY kod
                  , kraj 
             HAVING count(*) > 1 ) AS dupes
    INNER
      JOIN fvpoz
        ON fvpoz.kod = dupes.kod         
       AND fvpoz.kraj = dupes.kraj
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Thanks. Now came to my mind such an idea. Is it possible to query this table to change the number of records by joining them into one record?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's possible, but you need to create an extra table to do it (you can later drop/rename)

    also, you need to decide what to do with all the other columns besides kod and kraj
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    Super. I thought about the same for a long time but as I am self taught with no experience of my ideas seem to me often childish but I'm not a child anymore. I have another idea for algorithm on the above my problem.
    I would like to update the first record of the results of grouping and other records that were involved in grouping to remove from the table. Another words. Your query will allow me to determine which records will be grouped. The first record of this list will be updated and the rest records will be removed from the table. Is it worth taking the time this idea?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    Is it worth taking the time this idea?
    the answer to this question is somewhere between "no, don't bother" and "yes, right away"

    it depends on what your table is for and how you use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2012
    Posts
    91
    I tried to modify this code but without effect. So again I ask for help.
    The problem is when i use such a table:

    ID | FVNAG | KOD | KRAJ
    1........1...601.....D
    2........2...601.....D
    3........1.......455......D
    4........1.......333......D
    11......1....601....D
    6........2.......456......D
    13......2....601....D

    Your originally cod return ID value: 1,2,11,13 and I wonder why there is a value 2 and 13 if condition in code is: WHERE fvnag=1 so i except only ID: 1 and 11.
    But I solved this problem adding clause WHERE fvnag=1 at the end of Your code.

    SELECT fvpoz.id
    , fvpoz.kod
    , fvpoz.kraj
    FROM ( SELECT kod
    , kraj
    FROM fvpoz
    WHERE fvnag=1
    GROUP
    BY kod
    , kraj
    HAVING count(*) > 1 ) AS dupes
    INNER
    JOIN fvpoz
    ON fvpoz.kod = dupes.kod
    AND fvpoz.kraj = dupes.kraj
    WHERE fvnag=1

    But another problem is when i try to include to this cod more values from FVNAG

    SELECT fvpoz.id
    , fvpoz.kod
    , fvpoz.kraj
    FROM ( SELECT kod
    , kraj
    FROM fvpoz
    WHERE fvnag=1 AND fvnag=2
    GROUP
    BY kod
    , kraj
    HAVING count(*) > 1 ) AS dupes
    INNER
    JOIN fvpoz
    ON fvpoz.kod = dupes.kod
    AND fvpoz.kraj = dupes.kraj
    WHERE fvnag=1 AND fvnag=2

    in such a case i got empty set but I expected ID: 1,2,11,13.
    Can you explain how to obtain aim?
    Last edited by duf; 07-03-12 at 09:41.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please use [code]...[/code] tags

    Code:
    SELECT fvpoz.id
         , fvpoz.kod
         , fvpoz.kraj
      FROM ( SELECT kod
                  , kraj
               FROM fvpoz 
              WHERE fvnag IN ( 1 , 2 )
             GROUP 
                 BY kod
                  , kraj 
             HAVING count(*) > 1 ) AS dupes
    INNER
      JOIN fvpoz
        ON fvpoz.kod = dupes.kod         
       AND fvpoz.kraj = dupes.kraj
       AND fvpoz.fvnag IN ( 1 , 2 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    please use [code]...[/code] tags

    Code:
    SELECT fvpoz.id
         , fvpoz.kod
         , fvpoz.kraj
      FROM ( SELECT kod
                  , kraj
               FROM fvpoz 
              WHERE fvnag IN ( 1 , 2 )
             GROUP 
                 BY kod
                  , kraj 
             HAVING count(*) > 1 ) AS dupes
    INNER
      JOIN fvpoz
        ON fvpoz.kod = dupes.kod         
       AND fvpoz.kraj = dupes.kraj
       AND fvpoz.fvnag IN ( 1 , 2 )
    Of course, sorry. Thank You.

  10. #10
    Join Date
    Jan 2012
    Posts
    91
    Code:
    SELECT fvpoz.id
         , fvpoz.kod
         , fvpoz.kraj
      FROM ( SELECT kod, kraj, wt, wd, wtr
      
      LEFT OUTER JOIN twt ON twt.id=fvpoz.id
      LEFT OUTER JOIN twd ON twd.id=fvpoz.id 
      LEFT OUTER JOIN twtr ON twtr.id=fvpoz.id
    
               FROM fvpoz 
              WHERE fvnag IN ( 1 , 2 )
             GROUP 
                 BY kod
                  , kraj 
             HAVING count(*) > 1 ) AS dupes
    INNER
      JOIN fvpoz
        ON fvpoz.kod = dupes.kod         
       AND fvpoz.kraj = dupes.kraj
       AND fvpoz.fvnag IN ( 1 , 2 )
    I try to add to your code three tables and these bold are correct but how link them with dupes alias?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    ... these bold are correct ...
    no, they're not

    have you looked up the correct syntax for the FROM clause yet?

    you really should learn how to use da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    no, they're not
    have you looked up the correct syntax for the FROM clause yet?
    Ok, I made mistake in example above, it shoud be:
    Code:
    SELECT fvpoz.id
         , fvpoz.kod
         , fvpoz.kraj
      FROM ( SELECT kod, kraj, wt, wd, wtr FROM fvpoz 
      
      LEFT OUTER JOIN twt ON twt.id=fvpoz.id
      LEFT OUTER JOIN twd ON twd.id=fvpoz.id 
      LEFT OUTER JOIN twtr ON twtr.id=fvpoz.id
    
              WHERE fvnag IN ( 1 , 2 )
             GROUP 
                 BY kod
                  , kraj 
             HAVING count(*) > 1 ) AS dupes
    INNER
      JOIN fvpoz
        ON fvpoz.kod = dupes.kod         
       AND fvpoz.kraj = dupes.kraj
       AND fvpoz.fvnag IN ( 1 , 2 )
    Quote Originally Posted by r937 View Post
    you really should learn how to use da manual
    Ok, i`m working on it, but not everything there is as simple as example. The last problem with INSERT ... VALUE(S) i solved myself with help of manual. The present problems i also solved myself with the help of manual
    Code:
    SELECT fvpoz.id
         , fvpoz.kod
         , fvpoz.kraj
      FROM ( SELECT kod, kraj, wt, wd, wtr FROM fvpoz 
      
      LEFT OUTER JOIN twt ON twt.id=fvpoz.id
      LEFT OUTER JOIN twd ON twd.id=fvpoz.id 
      LEFT OUTER JOIN twtr ON twtr.id=fvpoz.id
    
              WHERE fvnag IN ( 1 , 2 )
             GROUP 
                 BY kod
                  , kraj 
             HAVING count(*) > 1 ) AS dupes
    INNER
      JOIN fvpoz
        ON fvpoz.kod = dupes.kod         
       AND fvpoz.kraj = dupes.kraj
       LEFT OUTER JOIN TWT ON TWT.ID=dupes.wt LEFT OUTER JOIN TWD ON TWD.ID=dupes.wd LEFT  OUTER JOIN  TWTR ON   TWTR.ID=dupes.wtr
       AND fvpoz.fvnag IN ( 1 , 2 )
    but I would like to see your opinions.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    but I would like to see your opinions.
    in my opinion, joining to 3 tables with LEFT OUTER JOINs, and then not using any of their columns, is totally useless
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    in my opinion, joining to 3 tables with LEFT OUTER JOINs, and then not using any of their columns, is totally useless
    This query return records in which the action is performed like summing and deleting records from the table. These steps are performed in the while loop.

Posting Permissions

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