Selecting one row per field

[azzazzello]

Suppose I have a table consisting of 10 fields.

f1,f2,...,f10

f1 is not the primary key, and hence has repetitions

suppose I have

the following set of data

AAA,1,2,...
BBB,3,4,....
AAA,5,9,...
CCC,4,1,...
BBB,3,7,...

I need to return any ENTIRE row for a distinct value of f1.
So ideally my result would be

AAA,1,2,...
BBB,3,7,...
CCC,2,3,...

thous is can be ANY of the repeating rows, as long as there is just ONE in the resultant set

 

[azzazzello]

i just realized that I can just group by f1 in MySQL, however, unfortunately, my database is Oracle and does not allow having fields in the resultant set that are not in group. This forum having been most helpful in the past, I thought I would ask a generic SQL question here. Well, I will go ask in another forum I guess.

 

[DonQuichote]

Standard SQL is that you have to use aggregate functions for the columns that you are not GROUPing by. So you usually use MIN or FIRST if oracle supports it.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[azzazzello]

OK...but the problem is I want the first (or min or max or whatever - anything!) of every distinct f1. I am not sure how to get that...

 

[r937]

SELECT f1
     , MIN(f2)
     , MAX(f3)
     , AVG(f4)
     , MAX(f5)
     , MIN(f6)
     , MAX(f7)
     , AVG(f8)
     , MIN(f9)
     , MAX(f10)
  FROM daTable
GROUP
    BY f1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon