Select all rows from SQL based upon existence of multiple rows (sequence numbers)

Question

Let's say I have table data similar to the following:

123456 John  Doe 1  Green  2001
234567 Jane  Doe 1  Yellow 2001
234567 Jane  Doe 2  Red    2001
345678 Jim   Doe 1  Red    2001

What I am attempting to do is only isolate the records for Jane Doe based upon the fact that she has more than one row in this table. (More that one sequence number) I cannot isolate based upon ID, names, colors, years, etc... The number 1 in the sequence tells me that is the first record and I need to be able to display that record, as well as the number 2 record -- The change record.

If the table is called users, and the fields called ID, fname, lname, seq_no, color, date. How would I write the code to select only records that have more than one row in this table? For Example:

I want the query to display this only based upon the existence of the multiple rows:

234567 Jane  Doe 1  Yellow 2001
234567 Jane  Doe 2  Red    2001

In PL/SQL

Answer 1

check out hte HAVING clause

Answer 2

First, to find the IDs for records with multiple rows you would use:

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

So you could get all the records for all those people with

 SELECT * FROM table WHERE ID IN (SELECT ID FROM table GROUP BY ID HAVING COUNT(*) > 1)

If you know that the second sequence ID will always be "2" and that the "2" record will never be deleted, you might find something like:

 SELECT * FROM table WHERE ID IN (SELECT ID FROM table WHERE SequenceID = 2)

to be faster, but you better be sure the requirements are guaranteed to be met in your database (and you would want a compound index on (SequenceID, ID)).

Answer 3

INNER JOIN

JOIN:

SELECT u1.ID, u1.fname, u1.lname, u1.seq_no, u1.color, u1.date
FROM users u1 JOIN users u2 ON (u1.ID = u2.ID and u2.seq_no = 2) 

WHERE:

SELECT u1.ID, u1.fname, u1.lname, u1.seq_no, u1.color, u1.date
FROM users u1, thetable u2 
WHERE 
    u1.ID = u2.ID AND
    u2.seq_no = 2

Answer 4

Try something like the following. It's a single tablescan, as opposed to 2 like the others.

select * from ( select t1.*, count(name) over (partition by name) mycount from table t1) where mycount >1;