HARD TOP SQL PROBLEM PLEASE HELP

Hi,

I need to use a top and a join in the same sql. To get 10 top refnr from orders_refnr. That works fine to I use this:

SQL = "SELECT TOP 10 refnr, antal = COUNT(refnr) FROM orders_refnr INNER JOIN produkter ON (orders_refnr.refnr = produkter.referensnummer) GROUP BY refnr ORDER BY antal DESC"

But I need to be able to get information from more fields than the field refnr. How can I specify more fields? I need to get other fields from produkter.

Please helt I′m really stucked.

Using the Northwind sample database, here's how you could do it using the Max (or Min) function:
select top 10 orders.customerId, count(orders.customerId), Max(CompanyName)
from orders
inner join customers on orders.customerId = customers.customerId
group by orders.customerID
order by count(orders.customerId) desc
|||

thanks for you answer.

But the problem is that I want more fields in my rs than just orders.customerId. How can I specify more fields?

|||Try it like this:

SELECT

    refnr,

    antal,

    produkter.column1,

    produkter.column2

FROM

( 

SELECT TOP 10

    refnr,

    COUNT(refnr) AS antal

FROM

    orders_refnr

GROUP BY

    refnr

ORDER BY

    2 DESC     

) AS subquery

INNER JOIN

    produkter ON (subquery.refnr = produkter.referensnummer)

ORDER BY

    antal DESC

|||Add the fields you want to the select list and also add them to the group by list.