Selct DISTINCT

#1

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!

#2

Comments embedded.
On Jan 29, 12:33*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

I am looking to query the first occurance of a row based on a distinct
column.

Hmmm ...

Quote:

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

Yes, your set consists of one column of data.

Quote:

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

This is no longer a one-column set, yet it produces the correct
result.

Quote:

So it looks like distinct works on the row, looking for an entirely
distinct row.

Correct.

Quote:

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.

Quote:

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.

Quote:

Thanks!

David Fitzjarrell

#3

Comments embedded.
On Jan 29, 12:33*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

I am looking to query the first occurance of a row based on a distinct
column.

Hmmm ...

Quote:

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

Yes, your set consists of one column of data.

Quote:

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

This is no longer a one-column set, yet it produces the correct
result.

Quote:

So it looks like distinct works on the row, looking for an entirely
distinct row.

Correct.

Quote:

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.

Quote:

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.

Quote:

Thanks!

David Fitzjarrell

#4

Comments embedded.
On Jan 29, 12:33*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

I am looking to query the first occurance of a row based on a distinct
column.

Hmmm ...

Quote:

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

Yes, your set consists of one column of data.

Quote:

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

This is no longer a one-column set, yet it produces the correct
result.

Quote:

So it looks like distinct works on the row, looking for an entirely
distinct row.

Correct.

Quote:

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.

Quote:

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.

Quote:

Thanks!

David Fitzjarrell

#5

Comments embedded.
On Jan 29, 12:33*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

I am looking to query the first occurance of a row based on a distinct
column.

Hmmm ...

Quote:

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

Yes, your set consists of one column of data.

Quote:

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

This is no longer a one-column set, yet it produces the correct
result.

Quote:

So it looks like distinct works on the row, looking for an entirely
distinct row.

Correct.

Quote:

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.

Quote:

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.

Quote:

Thanks!

David Fitzjarrell

#6

trpost (AT) gmail (DOT) com wrote:

Quote:

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!

Quote:

SELECT * FROM TABLE

ID Name SSN
456 Todd 1235
955 Jane 2345
955 Jane
988 Jack 3547

No order by clause ...

Quote:

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:

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

Quote:

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!

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

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#7

trpost (AT) gmail (DOT) com wrote:

Quote:

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!

Quote:

SELECT * FROM TABLE

ID Name SSN
456 Todd 1235
955 Jane 2345
955 Jane
988 Jack 3547

No order by clause ...

Quote:

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:

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

Quote:

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!

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

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#8

trpost (AT) gmail (DOT) com wrote:

Quote:

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!

Quote:

SELECT * FROM TABLE

ID Name SSN
456 Todd 1235
955 Jane 2345
955 Jane
988 Jack 3547

No order by clause ...

Quote:

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:

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

Quote:

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!

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

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#9

trpost (AT) gmail (DOT) com wrote:

Quote:

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!

Quote:

SELECT * FROM TABLE

ID Name SSN
456 Todd 1235
955 Jane 2345
955 Jane
988 Jack 3547

No order by clause ...

Quote:

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:

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

Quote:

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!

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

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#10

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.