By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Help on a SQL query

P: n/a
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
Massi

Sep 14 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
) dt
on t.Company = dt.Company
and t.Importance = dt.Importance

Simon

Sep 14 '05 #2

P: n/a
Unfortunately is not working ...

It returns the following result set:

Contact Company
Job Title Importance
1000000000001637472 1000000000001587309 CCM 200
1000000000001637472 1000000000001587309 CCM 200
1000000000001637472 1000000000001587309 CCM 200
1000000000001637472 1000000000001587309 CCM 200

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

Sep 14 '05 #3

P: n/a
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

Sep 14 '05 #4

P: n/a
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

Simon

Sep 14 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.