How to select the rows where each first instance of a certain column value is found i

FiRe5tOrM
04-14-2004, 07:56 AM
How to select the rows where each first instance of a certain column value is found in SQL


Say there are 3 columns CarManufacture, CarModel & CarAge and the column CarManufacture contained FORD,FORD,FIAT,TOYOTA, FORD etc.

How in SQL would you display the row data for each first time a car manufacture appears in the CarManufacture column please? as im stumped


Thx in advance

FiRe5tOrM

rufen101
04-14-2004, 09:29 AM
Select * from table where carManufacture like 'desired car manufacture';

I think it's what your looking for.

FiRe5tOrM
04-14-2004, 09:48 AM
Select * from table where carManufacture like 'desired car manufacture';

I think it's what your looking for.


That would display all the records that match a cetain car manufacture woudlnt it? I would like to display just one record for each car manufacture found (The first one found) and the car manufactures in the table are unknown till the sql statement finds an instance of them.

reboot
04-14-2004, 09:49 AM
Select Distinct carManufacture from table where carManufacture like 'desired car manufacture'

FiRe5tOrM
04-14-2004, 10:57 AM
thx for all your help but im proberly not explaing it very well

i want the sql statement to search through each record and displaying all of the fields (CarManufacture, CarModel, CarAge, etc ) if its the first time its seen a record with that car manufacture i.e. (Ford). While the statement carries on searching if it finds another record with car manufacture Ford it ignores it and moves onto the next record.

so if the table was;

CAR MANUFACTURE CAR MODEL CAR AGE
FORD FOCUS 3
TOYOTA CELICA 4
FORD ESCORT 8
FIAT PUNTO 1
FIAT PANDA 2
FORD GT 1
JAGUAR XKR 3

The restults would be


CAR MANUFACTURE CAR MODEL CAR AGE
FORD FOCUS 3
TOYOTA CELICA 4
FIAT PUNTO 1
JAGUAR XKR 3

if its not possible to use SQL ado is fine. i hope that explains my problem better :)

FiRe5tOrM
04-14-2004, 05:26 PM
any ideas please?


I tried something like this;

SELECT CarManufacture, CarModel, CarAge FROM table1 RIGHT JOIN (Select Distinct CarManufacture From table1 ) ON table1.CarManufacture = table1.Carmanufacture


although my syntax is wrong i thought something like that might work? but unfortatly i havent been able to get it to :/

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum