query for 2 most recent periods 5

[robertsquestion]

Hi,

I'm using Microsoft Access 2003 and got the following question.
Table T_Sales contains the following fields:
Customernr
Date
Sales

Now I'm looking for a query that gives me the 2 records (per customernr) that contain the largest sales.
So suppose the table contains (custnr, date, sales) the following:
1000, 01-01-2006, 500
1000, 02-01-2006, 900
1000, 03-01-2006, 200
2000, 01-01-2006, 850
2000, 02-01-2006, 600
2000, 03-01-2006, 900

The result of the query should be:
1000, 01-01-2006, 500
1000, 02-01-2006, 900
2000, 01-01-2006, 850
2000, 03-01-2006, 900

Does anyone know how to solve this in a query?
Thanks in advance for your help!

Robert
The Netherlands

 

[Crowley16]

groupings and top could so this...

select top 2 flds from tableName group by customer order by sales desc



--------------------
Procrastinate Now!

 

[robertsquestion]

Hi Crowley,

Thanks for your reply. I tried "select top x", but still have some problems.
I've created the following query:

SELECT TOP 2 T_Sales.Customernr, T_Sales.Date, T_Sales.Sales
FROM T_Sales
GROUP BY T_Sales.Customernr, T_Sales.Date, T_Sales.Sales
ORDER BY T_Sales.Customernr, T_Sales.Sales DESC;

And with the example-records mentioned above, the result is:
1000, 02-01-2006, 900
1000, 01-01-2006, 500

So I don't get the records for customer 2000, something's going wrong with the grouping. The query should give the maximum 2 sales-records per(!) customer.

Any idea what's going wrong?

Thanks a lot for your help.
Robert

 

[lespaul]

that's only going to return the top 2 records, not the top 2 records PER account.

It can be done, but I'd have to search the forum to figure it out (don't remember exactly how it's done!). If PHV doesn't come along and post the answer, I'll see what I can find to help.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[robertsquestion]

Hi Lespaul,

ok, please let me know if you find something, appreciate your help!

Regards,
Robert

 

[lespaul]

Something like this (it's probably close, but not quite right though!):

SELECT *
FROM Table T_Sales AS A
WHERE A.custnr In (SELECT TOP 2 custnr
FROM Table T_Sales WHERE custnr=A.custnr GROUP BY custnr ORDER BY Sales DESC)
ORDER BY 1, 3;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lespaul]

don't need the group by in the subquery! may need to change the order by clause as well!

 

[Golom]

I'm no PHV but here's a start

SELECT TOP 2 A.Customernr, A.[Date], A.Sales
FROM T_Sales A

WHERE A.Sales IN 

(
SELECT TOP 2 X.Sales
FROM T_Sales X
WHERE X.Customernr = A.Customernr 
  AND X.[Date]     = A.[Date]
ORDER BY X.Sales DESC
)

ORDER BY A.Customernr, A.Sales DESC

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[robertsquestion]

Hi Golom,

Thanks for your code. I've copied the exact code in a query but it gives me the same result-records:
1000, 02-01-2006, 900
1000, 01-01-2006, 500

So I still miss customer 2000. Any idea what's going wrong?

Thanks for your help!
Robert

 

[CosmoKramer]

Robert,

There may be a more elaborate way to accomplish this, but this is what has worked for me:

Create a query (qrySelectDistCustomers) that is nothing more than:

SELECT DISTINCT Customernr FROM T_Sales;

You main query SQL should look something like this after you add the subquery you just created to it and join on Customernr:

SELECT T_Sales.Customernr, T_Sales.[Date], T_Sales.sales
FROM T_Sales INNER JOIN Query14 ON T_Sales.Customernr = Query14.Customernr
WHERE (((T_Sales.sales) In (select top 2 sales from T_Sales where Customernr = [qrySelectDistCustomers].[Customernr] order by [sales] desc)))
ORDER BY T_Sales.Customernr, T_Sales.sales;

Hope this helps....

Si hoc legere scis, nimis eruditionis habes

 

[Golom]

Like I said ... not PHV ... and I haven't had enough coffee yet.

Remove the "TOP 2" from the first select.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[robertsquestion]

Hi Lespaul,

I've tried your code and without the GROUP BY it looks like this:

SELECT *
FROM T_Sales AS A
WHERE A.Customernr In (SELECT TOP 2 Customernr
FROM T_Sales WHERE Customernr=A.Customernr ORDER BY Sales DESC)
ORDER BY 1, 3;

But this query gives me all records...
Any ideas?

Thanks,
Robert

 

[robertsquestion]

Hi Golom,

Removing TOP 2 from first select gives me all records...
Any other options?

Thanks,
Robert

 

[lespaul]

can you post the SQL you're trying?

 

[r937]

stop with the top, already

select t1.custnr
     , t1.date
     , t1.sales
  from daTable as t1
inner
  join daTable as t2
    on t1.custnr = t2.custnr
   and t1.sales  <= t2.sales
group
    by t1.custnr
     , t1.date
     , t1.sales
having count(*) <= 2
order
    by t1.custnr
     , t1.sales desc

r937.com | rudy.ca

 

[robertsquestion]

Hi Cosmo, Lespaul and Golom,

Cosmo's suggestion is working!
So I'm using 2 queries now:

q_custno:
SELECT DISTINCT Customernr
FROM T_Sales;

q_total:
SELECT T_Sales.Customernr, T_Sales.Date, T_Sales.sales
FROM T_Sales INNER JOIN q_custno ON T_Sales.Customernr=q_custno.Customernr
WHERE (((T_Sales.sales) In (select top 2 sales from T_Sales where Customernr = [q_custno].[Customernr] order by [sales] desc)))
ORDER BY T_Sales.Customernr, T_Sales.sales;

And the result is the 4 records that I was looking for. Thanks Cosmo! And also thanks for the other suggestions.
By the way, Cramer really is the coolest character in Seinfeld! Just hope you're the real Cramer...

Robert

 

[robertsquestion]

Hi R937,

Wow, in 1 query. Works great, thanks!!

Robert

 

[PHV]

As I was mentioned in this thread, here what I'd posted if I were on line today:
SELECT A.*
FROM T_Sales AS A
WHERE Sales In (SELECT TOP 2 Sales
FROM T_Sales WHERE Customernr=A.Customernr ORDER BY 1 DESC)
ORDER BY 1, 3 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, forgot to say that I prefer Rudy's solution ...

 

[r937]

why, PHV, that's very complimentary, thank you so much

i respect your work a lot (i have certainly seen plenty of it), and to have this remark coming from you, i am honoured

please contact me offline (via my site), i wish to ask you something personal

r937.com | rudy.ca