Oracle/SQL - Returning semi-unique records

what I'm trying to do is go through a table a开发者_高级运维nd return semi-unique results based on a certain field. So for example with this data

field1  segment     field2  field3  field4  etc
----------------------------------------------- 
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S4          xxx     xxx     xxx     xxx
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S3          xxx     xxx     xxx     xxx

What I want to do is return 1 records of every segment type. The other data should come from the selected record, but which record that is doesn't matter as long as I get 1 unique record for each segment type.

field1  segment     field2  field3  field4  etc
----------------------------------------------- 
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S4          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S3          xxx     xxx     xxx     xxx

Hopefully that makes sense. Also this will run against Oracle so whatever the most efficient way to do this would be great (source table will have something like 10 mil records and 30 distinct segments)

---

You could do something like

SELECT segment,
       field1,
       field2,
       field3,
       field4
  FROM (SELECT segment,
               field1,
               field2,
               field3,
               field4, 
               row_number() over (partition by segment order by rownum) rnk
          FROM table_name)
 WHERE rnk = 1

This will pick an arbitrary row for each SEGMENT. When, in the future, someone decides that they do care what row is picked, you can adjust the ORDER BY in the analytic function.

---

I know this technique works when you only want to return the segment column for example.

    SELECT DISTINCT segment
    FROM YOUR_TABLE_NAME

But, when you try and query as so:

    SELECT DISTINCT SEGMENT, FIELD2, FIELD3
    FROM YOUR_TABLE_NAME
    WHERE BLAH = BLAH

This will return more than one row for each segment with different info in it.

This problem seems kinda similar to the problem found here.

Please comment if i am not understanding your problem correctly =)

---

If you don't want to type all the field names:

 SELECT * 
 FROM table_name t0 WHERE NOT EXISTS (
     SELECT * FROM table_name t1
     WHERE t1.segment = t0.segment
     AND t1.ROWNUM < t0.ROWNUM
     ;

I don't know exactly how exactly the tuple-id/ rownumber / object-id is called in oracle, but I'm sure it exists.