Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    21

    Unanswered: Excluding rows from duplicate result

    Hi,

    I have a table with many duplicate rows.
    Consider i have this data:
    Code:
    user | log_time
    -----------------
    1 | 12:00
    1 | 12:30
    1 | 12:45
    1 | 12:50
    1 | 13:00
    2 | 13:00
    3 | 13:00
    3 | 13:15
    4 | 13:27
    4 | 13:35
    To select duplicate rows:
    Code:
    select * from tbl_test where user in (select user from tbl_test group by user having count(*)>1)
    
    Result:
    user  |  log_time
    -----------------
    1      |   12:00
    1      |   12:30
    1      |   12:45
    1      |   12:50
    1      |   13:00
    3      |   13:00
    3      |   13:15
    4      |   13:27
    4      |   13:35
    4      |   12:00
    My situation is from the result above, i need to exclude 2 copies for the same user and the result should be look like this:
    Code:
    user  |  log_time
    -----------------
    1      |   12:00
    1      |   12:30
    1      |   12:45
    1      |   12:50
    1      |   13:00
    3      |   13:00
    3      |   13:15
    4      |   13:27
    4      |   13:35
    4      |   12:00
    
    Final result:
    user  |  log_time
    -----------------
    1      |   12:45
    1      |   12:50
    1      |   13:00
    4      |   12:00
    * italic font indicates the 2 same user rows that need to be excluded

    How can i do this? Really need help. Thanks all.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I don't understand this.

    Why is (1,12:30) a duplicate to (1,12:00) but (1,12:45) not for (1,12:50). They all have the same user_id

    i need to exclude 2 copies
    Do you mean to always exclude exactly two rows regardless of how many there are or what they contain?
    If yes, which two rows would that be?

  3. #3
    Join Date
    Jul 2010
    Posts
    21
    Hi,

    I am actually trying to exclude 2 rows for each user from the duplicate result.so, let say if user 1 got 5 duplicate rows and user 4 got 3 duplicate rows, it will only display 3 rows for user 1 and 1 row for user 4.. To be clear, I just want every user to exclude 2 rows.

    Or perhaps you can guide me how to select only 2 rows for each user.

    Thanks.
    Last edited by nbtet; 09-21-10 at 04:21.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by nbtet View Post
    Or perhaps you can guide me how to select only 2 rows for each user.
    The question is: which two rows?

    But something like this should work:
    Code:
    SELECT *
    FROM (
      SELECT user, 
             log_time, 
             row_number() over (PARTITION BY id ORDER BY log_time desc) as row_num
      FROM  tbl_test
    ) t
    WHERE row_num <= 2
    ;
    This will select the two latest (most recent) rows according to log_time.
    If you want the first (oldest) two rows, simply change the ORDER BY in the windowing function

  5. #5
    Join Date
    Jul 2010
    Posts
    21
    Hi Shammat,

    It doesn't matter which two rows, i just need to exclude any two rows for each user. Your code works perfectly! I really appreciate your sharing.

    Thank you very much!

Posting Permissions

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