Query to only display first record of a certain type? 1

[robojeff]

I have a table that contains records which may have
different attributes but I would like to list only one type part with the first list of attributes that I find for that part;

For example, my table may consist of the following fields:

Part# Color Price Shape

1111 Blue $20 Circle
2222 Red $15 Square
1111 Red $20 Circle
3333 Orange $10 Box
1111 Green $13 Ball

How do I set up my query so that I only get the
following results:

1111 Blue $20 Circle
2222 Red $15 Square
3333 Orange $10 Box

I tried the following but I still get all of the parts listed in the table:

SELECT First(shapes.[part#]) AS [FirstOfpart#], shapes.color, shapes.cost, shapes.Shape
FROM shapes
GROUP BY shapes.color, shapes.cost, shapes.Shape;

thanks

 

[PHV]

How do you know that Blue is the first color for Part# 1111 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks PHV-

In my application, it doesn't matter which attributes are found first for tthe first occurance of part# 1111.

I only wish to gather the data of the first found part# (regardless of which one it is) and would like to see one listing each for all other parts are in the table (Only displaying one part# for any attributes that may exist for it)... and yes, I do what the attributes even though they do not match from one record to the next...

thanks

 

[PHV]

I'd try something like this:

SELECT A.[part#], A.color, A.cost, A.Shape
FROM shapes AS A INNER JOIN (
SELECT [part#], First(color) AS FC FROM shapes GROUP BY [part#]
) AS B ON A.[part#]=B.[part#] AND A.color=B.FC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886