How to get the first occurrance of a value in a query 2

[jeep2001]

I need to produce a query...
One of the tables contains data like this...

Fm Type
--- -----
186 MM
186 BO
186 CP

I only want the first occurance. If the type is MM I want it and then move on, else IF it's "BO" I want the record, then move on, else if its CP I want it.
I am trying to do this in the query analyzer.
Any ideas.

 

[jeep2001]

NUMBER NAME SFAM_CD TYPE
186 xxxx INVESTMENTS & CO MM
186 xxxx INVESTMENTS & CO CP
186 xxxx INVESTMENTS & CO BO

I want the query to take only the MM, else The CP else the BO...In otherwords, 1 occurance.

 

[DotNetGnat]

Try...

SELECT Number, Name, CD, Type from yourtable A
WHERE Type IN (Select Top 1 Type from yourtable B where
A.Number=B.Number)

-DNG

 

[jeep2001]

Hi

THanks...I am trying it, but it runs very slowly due to large files.

 

[Melagan]

How recently was your DB compressed and reindexed? I tested this answer out on one of my tables with over 9000 records and it ran lightning fast over 100MB network (frontend and backend on fileserver).


~Melagan
______
"It's never too late to become what you might have been.

 

[jeep2001]

Well I am using DB2 linked tables. If I change the select statement in the sub query to EXISTS .... then it run lightning fast but does not return any records, if I use IN....then its slow...

 

[jeep2001]

You were rightabout speed, I imported the tables and it ran quickly. I have since lost my connection to the office computer, so I will have to wait till Tuesday.
Again, if If the data looks like this...

186 MM
186 BO
186 CP

187 CP
187 BO

My result set should be 186 MM
187 CP

Will the Top 1 give me this...

 

[jeep2001]

Worked like a charm with the tables imported...

Thanks