Microsoft Access - SQl to select top 5 from each group in n groups
Asked By Cherifa Hima
18-Jul-11 09:28 AM
Hi,
I have a table of data : affiliate, lenders, pub rate, sales rate, and leads. I want to find top 5 from each affilialte ordered by the leads. I want to use SQL language.
for example:
| Affilate |
Lenders |
Pub rate |
sale rate |
leads |
| Chima |
A1 |
2 |
3 |
4 |
| Chima |
A2 |
3 |
2 |
5 |
| Chima |
A3 |
4 |
3 |
4 |
| Chima |
A18 |
19 |
18 |
28 |
| Chima |
A19 |
20 |
19 |
30 |
| Barm |
b1 |
21 |
20 |
32 |
| Barm |
b2 |
22 |
21 |
34 |
| Scarese |
C3 |
37 |
36 |
64 |
| Scarese |
C4 |
38 |
37 |
66 |
| Scarese |
C5 |
39 |
38 |
68 |
| Scarese |
C6 |
40 |
39 |
70 |
| Scarese |
C7 |
41 |
40 |
72 |
Riley K replied to Cherifa Hima
The below is the query to select the top 3 products by quantity for each category in the Northwind database
-- TOP 3 Products By Quantity for each Category
USE NORTHWIND;
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY UnitsInStock DESC) AS 'RowNo',
CategoryName, ProductName, UnitsInStock FROM Categories c
INNER JOIN Products p ON c.CategoryID = p.CategoryID
)
SELECT CategoryName, ProductName, UnitsInStock FROM CTE
WHERE RowNo <= 3
Hope you got some idea
Vickey F replied to Cherifa Hima
You can rank by group or, for a relatively small table, use a subquery like:
SELECT a.affiliate, a.lenders, a.pubrate, a.salesrate, a. leads
FROM tblPrices AS a
WHERE a.salesrate= (SELECT TOP 5 b.salesrate
FROM tblPrices AS b
WHERE b.Lenders= a.Lenders
ORDER BY leads DESC)
Hope this will help you.
Cherifa Hima replied to Vickey F
Hi,
It is giving me syntax error in query expression in a.salerate= (SELECT TOP 5 b.salerate FROM Table 1 AS b WHERE b.Lenders= .Lenders ORDER BY leads DESC). Can this be used for access 2003.
This is what I wrote:
SELECT a.affilate, a.lenders, a.pubrate, a.salerate, a. leads
FROM Table1 AS a
WHERE a.salerate= (SELECT TOP 5 b.salerate
FROM Table 1 AS b
WHERE b.Lenders= a.Lenders
ORDER BY leads DESC)
Devil Scorpio replied to Cherifa Hima
Hi Cherifa,
Please try this query to find top 5 from each affilialte ordered by the leads.
I tried it, its working fine.
suppose the Tablename is Table1
SELECT affiliate, lenders, [pub rate],[sale rate],leads
FROM Table1
WHERE [Pub rate] IN (
SELECT TOP 5 [pub rate]
FROM Table1 as S
WHERE S.affiliate = Table1.affiliate
ORDER BY leads DESC
);
join i want to join • INNER JOIN - Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data. Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity Just to add a little commentary to the basic definitions above, in general the INNER JOIN option is considered to be the most common join needed in applications and / or
inner join and outer join Hai, What is the inner join and outer join? How it is used and What is the purpose of this? Thanks & Regards Suresh.K An Inner Join compares two tables based on some values and rows that satisfy the join predicate are
sql query difference betweeen left outer join and left inner join You use INNER JOIN to return all rows from both tables where there is a match. ie. in the resulting table all the rows and colums will have values. LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table. Hi Sankar Left Outer Join - -- -- -- -- -- -- -- -- -- -- -- Even if both table does not have any common data it will still return all the data from left table, unlike Left join, so it means that left outer join will return all the rows that an inner
usign inner join in subselect SQL Server The following SQL gives me different results than if I use " = " rather than "inner join" in the 2 subselects in the select statement. The inner join sub selects give me one blanket value for the whole column whereas the " = " subselects give me distinct values for each row. I have tried putting an alias on the purchase table however then the subselect gives message of "invalid object name" when I refer to the alias. If I jsut replace inner join with = it works fine but I was wondering if anyone had an explanation? thanks
Are parenthesis for join order ever important? SQL Server If i wanted to join three tables that are linked: TableA < = = > TableB < = = > TableC i always write the join as: FROM TableA INNER JOIN (TableB INNER JOIN TableC ON TableB.b = TableC.b) ON TableA.a = TableB.a nexting the joins with parenthesis. And if the join was logically (in my head) TableA = = > TableB < = = > TableC ^ = = > TableD i would do it (ommiting the