Select rows with specified distinct columns

[hakihara]

Hello everybody, I have this problem: I need to select a number of rows from a table that differ only by a specified column(s).For example, I have this table:

Table1: Case Element Value
======  ------------------
        A    1       11   
        A    1       23   
        B    2       41   
        B    2       17

and I would like to obtain this table(composed of the rows that contain different values in the [Element] column. The returned rows are those found first):

Table2: Case Element Value
======  ------------------
        A    1       11   
        B    2       41

Thanks for your time . I work with MSAccess.

 

[JarlH]

There's nothing "first" in SQL. The order of a table is always undefined. (You can use ORDER BY to order the result table AFTER it has been created.)

So you'll have to decide if you want the smallest/highest etc value. E.g.:

[tt]SELECT "Case", "Element", MIN("Value")
FROM Table1
GROUP BY "Case", "Element"
[/tt]

Note that CASE, ELEMENT, and VALUE are all reserved words in SQL, that's why I've double quoted them above. (See

http://developer.mimer.com/validator/sql-reserved-words.tml

for all full list of reserved words.)