show disinct records?

[ronaldmacdonald]

Hi

I am looking to select distinct records from a table based on the values being distinct in one row.

Consider the table "mydata":

field1 field2 field3 field4
a 1 b y
a 1 b y
b d f x

In this example table field4 has 2 rows with duplicate values (this is the field we wish to carry out the distinct operation on).

The output required would be:

field1 field2 field3 field4
a 1 b y
b d f x

If I do a select distinct(field4) all I get is:

field4
y
x

but I need to see the other colums as well!

 

[dbomrrsm]

select field1, field2, field3, field4
from yourtable
where field4 = (select distinct(field4) from yourtable)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Pattycake245]

select distinct field1, field2, field3, field4 from mytable

Tim

 

[Pattycake245]

DBomrrsm,

You will get a "subquery returns more than 1 record" error from the sub-query since there are two distinct values for field4.

Tim

 

[dbomrrsm]

it doesnt my query returns all three rows a subquery can return as many rows as you like.
what is ronaldmacdonald actually after

In this example table field4 has 2 rows with duplicate values (this is the field we wish to carry out the distinct operation on).

?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Pattycake245]

I guess if it were only a filter on field4 which set of data do you display when the other three fields are not the same. Something like:

select min(a.field1), min(a.field2), min(a.field3), a.field4
from mytable a
join (select distinct field4 from mytable) b
on a.field4=b.field4
group by a.field4

Tim

 

[dbomrrsm]

Could be - need to hear back from Ronald I feel

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[ronaldmacdonald]

Thanks guys,

DBomrrsm - I got the "subquery returns more than 1 record" error as well.

PattyCake245 - I got an error message regarding bit datatypes being used in the MIN argument.

To help clarify:

Field1 Field Field3 Field4
1 a 9 y
2 a 9 y
3 a 9 x


I want to select the distinct based only on Field4, if the other fields share the same values then the first record of the distinct selection should be returned. So the results would be:


Field1 Field Field3 Field4
1 a 9 y
3 a 9 x

 

[swampBoogie]

I want to select the distinct based only on Field4, if the other fields share the same values then the first record of the distinct selection should be returned

There is no concept of first or last in a RDBMS. You cannot assume that records will be returned in the same order as they were inserted (unless you have an order by on some column that reflects that order.)

You can do selection on a column having the lowest/highest value in a group.

select field1,field2,field3,field4 
from t as q
where field1 in 
   (select min(field1)
      from t
     where field4 = q.field4)

 

[Pattycake245]

ronaldmacdonald,

I was just guessing at the data types of some of your fields and the min stuff was just an example as to how you could pull the desired row. Smapboogie is correct, and I think if you did want to return the first occurence, the closest you could come would be to add a field to your query to give each row a unique id in order to pull that particular row of data. There have been recent posts on this very topic, if you wanted to go that route.

Tim