I am trying to do a search query that finds a single manufacturer tha

[tomk]

I am trying to do a search query that finds a single manufacturer that shows up the most in the search results. I have been able to get the search to tell me each manufacturer and the number of times it shows up, but I need to be able to select the manufacturer with the most duplicates. <br>
<br>
I have attached my code.<br>
<br>
SELECT DISTINCTROW First(Products.Manufacturer) AS [Manufacturer1],<br>
Count(Products.Manufacturer) AS Amount1<br>
FROM Products<br>
WHERE Search_Key LIKE '#PN4#%'<br>
GROUP BY Products.Manufacturer<br>
<br>
The Results look like:<br>
<br>
ABC - 5<br>
DEF - 12<br>
GHI - 2<br>
<br>
I want to be able to automatically select DEF since it has the most duplicates. I know that I need to perform a sub-query on the first query to find the max of Amount1, but I can't see to get it to work.<br>
<br>
Any help is greatly appreciated.<br>
<br>
Thanks,<br>
Tom Komin

 

[calahans]

Hi Tom,<br>
<br>
I have a solution using a view on your original table:<br>
<br>
create view VIEW_NAME<br>
as<br>
select manufacturer,count(manufacturer)'number'<br>
from YOUR_TABLE<br>
group by manufacturer<br>
<br>
select manufacturer,number from VIEW_NAME<br>
where number = (select max(number) from <br>
VIEW_NAME)<br>
<br>
This should work<br>
C

 

[kalyan]

Hi,<br>
If you try the order by clause with Descending option, you get the DEF in the beginning.<br>
SELECT DISTINCTROW First(Products.Manufacturer) AS [Manufacturer1],<br>
Count(Products.Manufacturer) AS Amount1<br>
FROM Products<br>
WHERE Search_Key LIKE '#PN4#%'<br>
GROUP BY Products.Manufacturer<br>
ORDER BY Count(Products.Manufacturer) DESC<br>

 

[calahans]

Hi,<br>
<br>
The FIRST keyword must be used in conjuction with FETCH on a cursor which you must create. <br>
<br>
If you try the ORDER BY solution you end up with a recordset instead of a single record, if you abandon the FIRST keyword.<br>
<br>
I would still advise using the view.<br>
<br>
C