0

Problem: There is a conference. Each class in the conference will run three times (time1, time2, time3). Attendee can only go to one session per class. I'm looking for duplicate class registrations. (User is going to class1: time1 and time2)

I need to write a query to find duplicate registrations with information in 3 different tables. The tables I'm joining are (class, user, registration). I need to find all duplicates that are created by adding the values for two columns. (class.title + user.id#) = duplicate.

I need the query to display only the duplicate rows found plus display additional column information such as id number, title, first, last, status.

For Example: The search would find these results

table

But display only... results

I'm not sure where to start. The information in class.title + user.id# will vary per row, so I can't search by specific information. (ie: user.id#=45624).

CarolBEE
  • 1
  • 2
  • It would be useful if you clarify the programming language you are using and the code that you have. – Angie Quijano May 03 '16 at 21:15
  • 1
    Possible duplicate of [Finding duplicate values in a SQL table](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) –  May 03 '16 at 22:59

2 Answers2

1

If I understood you right is the DISTINCT-Keyword what you are looking for: http://www.w3schools.com/sql/sql_distinct.asp

An example:

SELECT * FROM (SELECT DISTINCT title, status, first, last FROM your_table)

This will make sure that there will be no duplicate rows returned identified by title, status, first and last.

I may misunderstood your question. In case you are looking for duplicates only, consider the following code. This will return you all duplicate rows identified by title, status, first and last and give you id as an additional information.

SELECT id, title, status, first, last
    FROM your_table
GROUP BY title, status, first, last
    HAVING COUNT(*) > 1

Which one is the answer you were looking for?

Marco de Abreu
  • 663
  • 5
  • 17
  • I want to only show the duplicate rows. I don't want to display distinct rows. The duplicate is not the same value located in two columns. I want to display duplicates that are created by adding the information in two columns together to determine the duplicate. for example: title1 + user#1 = duplicate (display) title2+user#1 = distinct (don't display) – CarolBEE May 03 '16 at 21:17
  • Sorry I don't really understand your example. Are two rows with the same `title` and `user#` duplicates in your opinion? – Marco de Abreu May 03 '16 at 21:33
  • Yes. If row 1 and row 2 have the same title and user#, it is a duplicate. – CarolBEE May 03 '16 at 22:09
  • 1
    Alright, this is exactly what my second solution will return to you. – Marco de Abreu May 04 '16 at 07:33
0

maybe this will work

    select * from(
    SELECT id, title, status, first, last ,
row_number() over ( partition by id, title order by status) rowid 
        FROM your_table
        ) x
        where rowid = 2
Kostya
  • 1,567
  • 1
  • 9
  • 15