Help on a SQL query

Hello guys,
maybe you can point me into the right direction on this SQL Query that
is driving me crazy:

I've got the following table:

Contact Company Job_Title Importance
1 Company A CEO 10
2 Company A DIR 9
3 Company B MAN 5
4 Company B DIR 9
5 Company C PRG 4

I need to extract a single value, for each company, with the highest
contact (through the "importance" field).

In the example, I would need to have the following results:

Contact Company Job_Title Importance
1 Company A CEO 10
4 Company B DIR 9
5 Company C PRG 4

...any idea about how can i do it ?

Thanks very much fo your help
MassiHere's one option:

select
t.Contact,
t.Company,
t.Job_Title,
t.Importance
from
dbo.MyTable t
join
(
select Company, max(Importance) as 'Importance'
from dbo.MyTable
) dt
on t.Company = dt.Company
and t.Importance = dt.Importance

Simon|||Unfortunately is not working ...

It returns the following result set:

Contact Company
Job Title Importance
10000000000016374721000000000001587309CCM200
10000000000016374721000000000001587309CCM200
10000000000016374721000000000001587309CCM200
10000000000016374721000000000001587309CCM200

I need the company field to be unique in the resultset. Moreover, I
don't understand where it has taken the value 200 for importance (on DB
values range from 0 to 170)...

Still thanks for your help
Massi|||group by is missing:

Here's one option:

select
t.Contact,
t.Company,
t.Job_Title,
t.Importance
from
dbo.MyTable t
join
(
select Company, max(Importance) as 'Importance'
from dbo.MyTable
-----
GROUP BY COMPANY
----
) dt
on t.Company = dt.Company
and t.Importance = dt.Importance

Simon|||Oops, sorry - I left out the GROUP BY:

select
t.Contact,
t.Company,
t.Job_Title,
t.Importance
from
dbo.MyTable t
join
(
select Company, max(Importance) as 'Importance'
from dbo.MyTable
group by Company
) dt
on t.Company = dt.Company
and t.Importance = dt.Importance

If this still doesn't work as you require, I suggest you post CREATE
TABLE and INSERT statements to show a test case:

http://www.aspfaq.com/etiquette.asp?id=5006

Simonsql