 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Oracle / Oracle Server / January 2008Selct DISTINCT
I am looking to query the first occurance of a row based on a distinct column. Here is an example result set: SELECT * FROM TABLE ID Name SSN 456 Todd 1235 955 Jane 2345 955 Jane 988 Jack 3547 If I do this I get the expected number of results: SELECT DISTINCT(ID) FROM TABLE; ID 456 955 988 However if I do this I get all rows despite the distinct keyword: SELECT DISTINCT(ID), Name, SSN FROM TABLE; ID Name SSN 456 Todd 1235 955 Jane 2345 955 Jane 988 Jack 3547 So it looks like distinct works on the row, looking for an entirely distinct row. What I want to do is look only at one column and return the first row occurance. How can this be done? I am using an Oracle 10G database. Thanks! Comments embedded. On Jan 29, 12:33 pm, trp...@gmail.com wrote: > I am looking to query the first occurance of a row based on a distinct > column. Hmmm ... > Here is an example result set: > [quoted text clipped - 13 lines] > 955 > 988 Yes, your set consists of one column of data. > However if I do this I get all rows despite the distinct keyword: > SELECT DISTINCT(ID), Name, SSN FROM TABLE; [quoted text clipped - 4 lines] > 955 Jane > 988 Jack 3547 This is no longer a one-column set, yet it produces the correct result. > So it looks like distinct works on the row, looking for an entirely > distinct row. Correct. > What I want to do is look only at one column and return > the first row occurance. If you choose the correct column such a feat is possible. > How can this be done? I am using an Oracle 10G database. You'll need to use a column you didn't define, have no control over, yet is present in every table that exists in Oracle: ROWID. You may need to play with this a bit, but an example would be: SQL> select id, name, ssn 2 from mytable 4 where rowid in (select min(rowid) from mytable group by id) 3 / ID NAME SSN ---------- -------------------- ---------- 955 Jane 2345 456 Todd 1235 988 Jack 3547 SQL> Your mileage may vary. > Thanks! David Fitzjarrell > I am looking to query the first occurance of a row based on a distinct > column. Here is an example result set: Define "first occurance" - data from an RDBMS gets presented unordered, and there is no guarantee that you will get the same ordered set next time, unless you specifically order it! > SELECT * FROM TABLE > [quoted text clipped - 3 lines] > 955 Jane > 988 Jack 3547 No order by clause ... > If I do this I get the expected number of results: > SELECT DISTINCT(ID) FROM TABLE; [quoted text clipped - 5 lines] > > However if I do this I get all rows despite the distinct keyword: Not if you had another row with (955, Jane), you don't: SQL> select * from blah; ID NAME SSN ---------- ---------- ---------- 456 Todd 1235 966 Jane 2345 966 Jane 988 Jack 3547 966 Jane SQL> SELECT DISTINCT(ID), Name, SSN FROM blah; ID NAME SSN ---------- ---------- ---------- 456 Todd 1235 966 Jane 988 Jack 3547 966 Jane 2345 > SELECT DISTINCT(ID), Name, SSN FROM TABLE; > [quoted text clipped - 11 lines] > > Thanks! inline view, subquery, analytic functions pop to mind. The exact syntax is left for the OP, depending on how many columns there are to be inspacted.  SignatureRegards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up Let me expand a little on what I am actually doing, I provided a small example in the original post as I thought there might be a simple way to select the first distinct row based on one column. Without specifics, here is how my query is structured: SELECT DISTINCT(ID), NAME, SSN FROM ( SELECT DISTINCT(ID), NAME, SSN FROM TABLE1 UNION ALL SELECT DISTINCT(ID), NAME, SSN FROM TABLE2 ) So the problem I have is that there is some duplicate data in TABLE1 and TABLE2, but TABLE1 data always has more data available then in TABLE2, so that is how I know if there is a duplicate to take the first row. >Let me expand a little on what I am actually doing, I provided a small >example in the original post as I thought there might be a simple way [quoted text clipped - 17 lines] >TABLE2, so that is how I know if there is a duplicate to take the >first row. Use UNION instead of UNION ALL, duplicates will be discarded. trpost@gmail.com schrieb: > So the problem I have is that there is some duplicate data in TABLE1 > and TABLE2, but TABLE1 data always has more data available then in > TABLE2, so that is how I know if there is a duplicate to take the > first row. You seem to miss Frank's point - there is no FIRST row in the relational table (or in result set returned without order by clause). Either you introduce ordering criteria and then utilize analytical functions to eliminate *duplicates* or you take the approach - any one of them is as good as another and remove all *duplicates* except a random one, utilizing method you was shown by David. Best regards Maxim > Let me expand a little on what I am actually doing, I provided a small > example in the original post as I thought there might be a simple way [quoted text clipped - 17 lines] > TABLE2, so that is how I know if there is a duplicate to take the > first row. Maybe this is what you want: SELECT id, name, ssn FROM Table1 UNION SELECT id, name, ssn FROM Table2 WHERE NOT EXISTS (SELECT id FROM Table1 WHERE id = Table2.id); -- David Portas |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.