CODEWITHSUNDEEP
oracle-databasefunctioncountrows
Pezz
Pezz

Reputation: 21

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

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

Upvotes: 2

Views: 13280

Answers (4)

erbsock
erbsock

Reputation: 1217

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;

Upvotes: 2

Randy
Randy

Reputation: 16677

Check out the HAVING clause for a summary query. You can specify stuff like

  HAVING COUNT(*) >= 2

and so forth.

Upvotes: 0

kevpie
kevpie

Reputation: 26098

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

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50990

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

Upvotes: 4

Related Questions