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

Solution 1

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)).

Solution 2

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;

Comments

[kevpie]

This is where Oracle starts to shine.

[Jeffrey Kemp]

+1, however - looks like the data has a "person ID" as the first column - if that uniquely identifies a person I'd do the COUNT over that ID column instead (just in case there are two people with the same name).

[erbsock]

heh :) indeed, good catch on the person ID. Yes, you could easily replace "name" with "person ID".