SQL query TOP syntax not working

[Shweta]

Hi All,

Below is the query I used to combine all records from UK_Sale and US_Sale Tables.

select Emp_Name, sum(sales)as TotalSales
from uk_sale 
group by emp_name
union all
select Emp_Name, sum(sales)as TotalSales
from us_sale 
group by emp_name
order by TotalSales desc

I got below table as a result of the above query:

Emp_Name	TotalSales
F	4000
H	3950
A	2500
G	2290
E	1950
B	500
C	250
D	200

<colgroup><col><col></colgroup><tbody>
</tbody>

Now from the above result I want to find out the fourth highest Sale along with employee name i.e. G with 2290. For this, I am thinking to use DISTINCT TOP 4 to get the top four records (as data is sorted) and then I will use MIN function to get the 4th record. but I am not sure how to apply this logic.

Kindly help.

Thanks in advance!

Regards,
Shweta Jain

 

[apr pillai]

I don't think we can do this in one Query.
Try this:

1. Save the Union Query with the Name: Query1.
2. Create another Query using Query1 as source, SQL is given below:

SELECT TOP 4 Query1.Emp_Name, Query1.TotalSales
FROM Query1;

3. Save the above Query with the Name: Query2
4. The next step, frankly I have never created one like this before but serves the purpose. SQL is given below:

SELECT DLast("Emp_Name","Query2","TotalSales=" & DMin("TotalSales","Query2")) AS EmpName, DMin("TotalSales","Query2") AS Total_Sales
FROM Query2
GROUP BY DLast("Emp_Name","Query2","TotalSales=" & DMin("TotalSales","Query2")), DMin("TotalSales","Query2");

 

[james_lankford]

this seems to work

select 
  top 1
  Emp_Name, 
  TotalSales
FROM 
(
SELECT 
  top 4 
  Emp_Name, 
  TotalSales
FROM 
  your_query 
order by 
  TotalSales Desc
)
order by 
  TotalSales Asc

 

[xenou]

TOP allows for ties. Is that a problem? I don't think you can guarantee results with TOP.