Hello All:

Here is my problem. I have a table named TABS with a lot of records in it. For simplicity, the field names are A, B, C, D, E, and IX.
The value of IX is unique for each record. Leaving out the IX field, the resulting fields yield numerous exact duplicates between records. IX is an integer
and as it happens, A, B, C, D, E are all strings, not that this really matters to the problem at hand.

I would like to reduce the table to a subset of records that are unique with respect to fields A, B, C, D, E and where each resulting record
contains the lowest value of IX from the set of records that are unique in their values for A, B, C, D, E.

Getting the unique (distinct) records for fields A, B, C, D, E is easy:

SELECT DISTINCT A, B, C, D, E FROM TABS

but the question I have is how to get the resultset to include IX and have IX be the
lowest value from the set of records where A, B, C, D, E are all the same.

Any suggestions is appreciated.

Thanks

Oliver

"Oliver" <dro@mettrix.com> wrote in message
news:27E46CDC-DAC6-471D-876B-B36806477A12@news.elevatesoft.com...
> Hello All:
>
> Here is my problem. I have a table named TABS with a lot of records in it.
> For simplicity, the field names are A, B, C, D, E, and IX.
> The value of IX is unique for each record. Leaving out the IX field, the
> resulting fields yield numerous exact duplicates between records. IX is an
> integer
> and as it happens, A, B, C, D, E are all strings, not that this really
> matters to the problem at hand.
>
> I would like to reduce the table to a subset of records that are unique
> with respect to fields A, B, C, D, E and where each resulting record
> contains the lowest value of IX from the set of records that are unique in
> their values for A, B, C, D, E.
>
> Getting the unique (distinct) records for fields A, B, C, D, E is easy:
>
> SELECT DISTINCT A, B, C, D, E FROM TABS
>
> but the question I have is how to get the resultset to include IX and have
> IX be the
> lowest value from the set of records where A, B, C, D, E are all the same.
>
> Any suggestions is appreciated.
>

SELECT MIN(IX) AS IX, A, B, C, D, E FROM TABS GROUP BY A,B,C,D,E

Robert

>
> Oliver
>

Robert

>
> SELECT MIN(IX) AS IX, A, B, C, D, E FROM TABS GROUP BY A,B,C,D,E
>
Congratulations

You are the SQL guy. Every SQL doubt I see in this NG you solved so easy.

Eduardo

Rober, super duper. Thanks.

Oliver


Robert" wrote:


"Oliver" <dro@mettrix.com> wrote in message
news:27E46CDC-DAC6-471D-876B-B36806477A12@news.elevatesoft.com...
> Hello All:
>
> Here is my problem. I have a table named TABS with a lot of records in it.
> For simplicity, the field names are A, B, C, D, E, and IX.
> The value of IX is unique for each record. Leaving out the IX field, the
> resulting fields yield numerous exact duplicates between records. IX is an
> integer
> and as it happens, A, B, C, D, E are all strings, not that this really
> matters to the problem at hand.
>
> I would like to reduce the table to a subset of records that are unique
> with respect to fields A, B, C, D, E and where each resulting record
> contains the lowest value of IX from the set of records that are unique in
> their values for A, B, C, D, E.
>
> Getting the unique (distinct) records for fields A, B, C, D, E is easy:
>
> SELECT DISTINCT A, B, C, D, E FROM TABS
>
> but the question I have is how to get the resultset to include IX and have
> IX be the
> lowest value from the set of records where A, B, C, D, E are all the same.
>
> Any suggestions is appreciated.
>

SELECT MIN(IX) AS IX, A, B, C, D, E FROM TABS GROUP BY A,B,C,D,E

Robert

>
> Oliver
>