Combined Table data with Duplicate removal by Ranking Value

First off, Is this the proper newsgroup for the question below?

If so we are having problems migrating from ACCESS mySQL to SQL Server
2000. Until now we used a combination of mySQL statements and ASP
code, now we need to know if we can do the following using only SQL
statements.

I have two conceptual database tables - Table A and Table B.

Table A has 2 fields; Company_ID (prikey), CompanyName
Table B has 4 fields; Link_ID (prikey), Company_ID, Descript and Rank

Results Table C is to consist of 3 fields drawn from A & B tables.

Link_ID, Company_ID, Descript and Rank

Using Tables A & B how can I create a Results Table C consisting of
instances where the word 'boats' is found in the Descrip field of
Table B. And where a company appears more than once in the results we
want to keep only the instance with the lowest 'Rank' value. In cases
where the Rank values are equal, either but not both shall be included
in the final record set.

Using the Sample data below I want to know if SQL statements can
generate the following following Results Table C data.

108, 1103, motor boats, 1
103, 1101, sail boats, 2
105, 1102, service boats, 5


Sample data

Table A - (Company_ID, CompanyName)

1101, Sunfish
1102, Dingy
1103, Speedboat

Table B - (Link_ID, Company_ID, Descript, Rank

101, 1101, All kinds of boats, 5
102, 1101, sailing gear, 3
103, 1101, sail boats, 2
104, 1102, oars and tackle, 3
105, 1102, service boats, 5
106, 1102, fun boats, 5
107, 1103, motors engines, 1
108, 1103, motor boats, 1
109, 1103, speed boats, 1
110, 1106, instruments, 5

Hi DRM,

  >In cases
  >where the Rank values are equal, either but not both shall be included
  >in the final record set.

There needs to be some logic behind selecting the records that are repeating.
in your case for company_id 1102, and 1103 rows are repeating for the same rank.
Therefore im bringing link_id into picture and getting max of link_id.

Try:

select b.* from tableb b,
(select max(link_id)link_id,company_id, min(rank) rank from tableb a
where descript like '%boats%'
group by company_id) a
where a.company_id = b.company_id and a.rank=b.rank and a.link_id= b.link_id



-- Vishal<!-- ~MESSAGE_AFTER~ -->