SELECT TOP n -- looking for assistance
in [General]
|
Prev: rere
Next: Entering measurements in a field
From: Hans Up on 3 Apr 2010 16:53 Clif McIrvin wrote: > I'm trying to write a query that will return the rows from the most > recent two weeks. I discovered that TOP 1 returns all the rows from the > most recent week, but to my dismay discovered that TOP 2 also returns > the same rows. On reflection, I believe that I understand why, which > leads to my dilemma: how do I return the rws from the last two weeks, > regardless of how many rows that might be? > > Here's what I have right now: > > SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, > AssignedLots.WeekEnding > FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON > Plants.PlantID=AssignedLots.PlantID) ON [Mix > Designs].MixID=AssignedLots.MixID > ORDER BY AssignedLots.WeekEnding DESC; > > which returns the last three weeks: > > Mix ID, LotNo, WeekEnding > 6PF, 10-040, 03-Apr-10 > SMH, 10-042, 03-Apr-10 > 7A, 10-039, 03-Apr-10 > 3BF, 10-041, 03-Apr-10 > 6PF, 10-037, 27-Mar-10 > 7A, 10-036, 27-Mar-10 > 3BF, 10-038, 27-Mar-10 > 6PF, 10-035, 20-Mar-10 > 3BF, 10-034, 20-Mar-10 > 1PF, 10-033, 20-Mar-10 > 7A, 10-032, 20-Mar-10 Hi Clif, Not sure how well I understand your problem. I wonder if it might help to create a separate query which only returns the last 2 unique WeekEnding values. Create qryLast2Weeks: SELECT TOP 2 l.WeekEnding FROM ( SELECT DISTINCT WeekEnding FROM AssignedLots ) AS l ORDER BY l.WeekEnding DESC; Then INNER JOIN qryLast2Weeks (ON AssignedLots.WeekEnding = qryLast2Weeks.WeekEnding) to your existing query. Regards, Hans
From: Clif McIrvin on 3 Apr 2010 18:30 Thanks, Tom. Looks like that should work ... as long as the data in the table is kept current, which it *should* be. <smile>. I'd wondered about a WHERE clause, but wasn't quite clear on how to proceed. Thanks! Clif "Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message news:0dser5pgkpi5uqo01oh4loipjfbaj3dhsb(a)4ax.com... > On Sat, 3 Apr 2010 11:20:09 -0500, "Clif McIrvin" > <clare.moe(a)nevergmail.com.invalid> wrote: > > If I understand you correctly, you need a WHERE clause, not a TOP > clause. Design your query, and in the WeekEnding column enter on the > Criteria row: >>=DateAdd("ww",-2,Date()) > This expression will count back two weeks from today. > > -Tom. > Microsoft Access MVP > > > >>Looking for help with a select query. >> >>I have a table containing a column: WeekEnding (Date/Time field). >> >>Each week will have a different number of rows. >> >>I'm trying to write a query that will return the rows from the most >>recent two weeks. I discovered that TOP 1 returns all the rows from >>the >>most recent week, but to my dismay discovered that TOP 2 also returns >>the same rows. On reflection, I believe that I understand why, which >>leads to my dilemma: how do I return the rows from the last two weeks, >>regardless of how many rows that might be? >> >>Here's what I have right now: >> >>SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, >>AssignedLots.WeekEnding >>FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON >>Plants.PlantID=AssignedLots.PlantID) ON [Mix >>Designs].MixID=AssignedLots.MixID >>ORDER BY AssignedLots.WeekEnding DESC; >> >>which returns the last three weeks: >> >>Mix ID, LotNo, WeekEnding >>6PF, 10-040, 03-Apr-10 >>SMH, 10-042, 03-Apr-10 >>7A, 10-039, 03-Apr-10 >>3BF, 10-041, 03-Apr-10 >>6PF, 10-037, 27-Mar-10 >>7A, 10-036, 27-Mar-10 >>3BF, 10-038, 27-Mar-10 >>6PF, 10-035, 20-Mar-10 >>3BF, 10-034, 20-Mar-10 >>1PF, 10-033, 20-Mar-10 >>7A, 10-032, 20-Mar-10 >> >>I'm going to use TOP 7, on the premise that any given week could have >>six rows, but seven rows is not likely. The downside is when there are >>only 2 or 3 rows in each week, causing the query to return three (or >>4) >>weeks instead of two. >> >>Suggestions? >> >>Thanks in advance! >> >>Clif >>
From: Clif McIrvin on 3 Apr 2010 18:42 "Hans Up" <hans.updyke(a)nospam.invalid> wrote in message news:upJry%2320KHA.4420(a)TK2MSFTNGP02.phx.gbl... > Clif McIrvin wrote: >> I'm trying to write a query that will return the rows from the most >> recent two weeks. I discovered that TOP 1 returns all the rows from >> the most recent week, but to my dismay discovered that TOP 2 also >> returns the same rows. On reflection, I believe that I understand >> why, which leads to my dilemma: how do I return the rws from the last >> two weeks, regardless of how many rows that might be? >> >> Here's what I have right now: >> >> SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, >> AssignedLots.WeekEnding >> FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON >> Plants.PlantID=AssignedLots.PlantID) ON [Mix >> Designs].MixID=AssignedLots.MixID >> ORDER BY AssignedLots.WeekEnding DESC; >> >> which returns the last three weeks: >> >> Mix ID, LotNo, WeekEnding >> 6PF, 10-040, 03-Apr-10 >> SMH, 10-042, 03-Apr-10 >> 7A, 10-039, 03-Apr-10 >> 3BF, 10-041, 03-Apr-10 >> 6PF, 10-037, 27-Mar-10 >> 7A, 10-036, 27-Mar-10 >> 3BF, 10-038, 27-Mar-10 >> 6PF, 10-035, 20-Mar-10 >> 3BF, 10-034, 20-Mar-10 >> 1PF, 10-033, 20-Mar-10 >> 7A, 10-032, 20-Mar-10 > > Hi Clif, > > Not sure how well I understand your problem. I wonder if it might > help to create a separate query which only returns the last 2 unique > WeekEnding values. > > Create qryLast2Weeks: > > SELECT TOP 2 l.WeekEnding > FROM ( > SELECT DISTINCT WeekEnding > FROM AssignedLots > ) AS l > ORDER BY l.WeekEnding DESC; > > Then INNER JOIN qryLast2Weeks (ON AssignedLots.WeekEnding = > qryLast2Weeks.WeekEnding) to your existing query. > > Regards, > Hans Hans, I like your lesson in using a subquery -- that's something I've not yet tried to get used to using. The concept is clear, and I notice that it is completely independant of today's date, which makes it a little more "idiot proof". In essence, if I do what you suggest, I'd be running three queries when I call this, correct? I suppose that's just a bit of arcane trivia that has little practical consequence in terms of actual performance that the user would ever notice. Clif
From: Clif McIrvin on 3 Apr 2010 18:47 Yeah, you got right back to where I started from. Appreciate you taking the time to clean up after yourself -- been there myself. [My late boss was fond of saying that if you never did anything wrong, you just weren't working hard enough!] Clif "Steve" <notmyemail(a)address.com> wrote in message news:ubu%23Bi10KHA.3412(a)TK2MSFTNGP05.phx.gbl... > Thinking more about this, this will not work. Please ignore. > > Steve > > > "Steve" <notmyemail(a)address.com> wrote in message > news:eZro3X10KHA.348(a)TK2MSFTNGP02.phx.gbl... >> Add a calculated field yo your query: >> WeekAssignedLot:DatePart("ww",[WeekEnding]) >> Set the sort for this field to Descending. >> Set the Top property to 2. >> >> Steve >> santus(a)penn.com >> >> >> "Clif McIrvin" <clare.moe(a)nevergmail.com.invalid> wrote in message >> news:OBnHJm00KHA.2196(a)TK2MSFTNGP05.phx.gbl... >>> Looking for help with a select query. >>> >>> I have a table containing a column: WeekEnding (Date/Time field). >>> >>> Each week will have a different number of rows. >>> >>> I'm trying to write a query that will return the rows from the most >>> recent two weeks. I discovered that TOP 1 returns all the rows from >>> the most recent week, but to my dismay discovered that TOP 2 also >>> returns the same rows. On reflection, I believe that I understand >>> why, which leads to my dilemma: how do I return the rows from the >>> last two weeks, regardless of how many rows that might be? >>> >>> Here's what I have right now: >>> >>> SELECT TOP 8 [Mix Designs].[Mix ID], AssignedLots.LotNo, >>> AssignedLots.WeekEnding >>> FROM [Mix Designs] INNER JOIN (Plants INNER JOIN AssignedLots ON >>> Plants.PlantID=AssignedLots.PlantID) ON [Mix >>> Designs].MixID=AssignedLots.MixID >>> ORDER BY AssignedLots.WeekEnding DESC; >>> >>> which returns the last three weeks: >>> >>> Mix ID, LotNo, WeekEnding >>> 6PF, 10-040, 03-Apr-10 >>> SMH, 10-042, 03-Apr-10 >>> 7A, 10-039, 03-Apr-10 >>> 3BF, 10-041, 03-Apr-10 >>> 6PF, 10-037, 27-Mar-10 >>> 7A, 10-036, 27-Mar-10 >>> 3BF, 10-038, 27-Mar-10 >>> 6PF, 10-035, 20-Mar-10 >>> 3BF, 10-034, 20-Mar-10 >>> 1PF, 10-033, 20-Mar-10 >>> 7A, 10-032, 20-Mar-10 >>> >>> I'm going to use TOP 7, on the premise that any given week could >>> have six rows, but seven rows is not likely. The downside is when >>> there are only 2 or 3 rows in each week, causing the query to return >>> three (or 4) weeks instead of two. >>> >>> Suggestions? >>> >>> Thanks in advance! >>> >>> Clif >>> >>> >> >> > >
From: Hans Up on 3 Apr 2010 19:20
Clif McIrvin wrote: > Hans, I like your lesson in using a subquery -- that's something I've > not yet tried to get used to using. The concept is clear, and I notice > that it is completely independant of today's date, which makes it a > little more "idiot proof". Good. I wasn't clear whether you wanted the last 2 weeks which were stored in the table or data from this week and the previous week. They might not be the same. So went with the most recent 2 weeks on file. BTW, you will discard "TOP 8" from your old query before joining in qryLast2Weeks, right? I didn't think to mention that before. Also, look at Allen Browne's site when you're ready to delve further into subqueries. He was clear discussions with examples. > In essence, if I do what you suggest, I'd be running three queries when > I call this, correct? I suppose that's just a bit of arcane trivia that > has little practical consequence in terms of actual performance that the > user would ever notice. I don't know what to say about the 3 queries thing, Clif. The database engine will figure out a query plan and ultimately handle it as one complex "thing". Certainly it will be more challenging for Access than if you weren't asking it to figure out which weeks to pull whenever it runs the query. What I mean is it would be easier on Access if you were able to define a simple WHERE clause: WHERE AssignedLots.WeekEnding = #2010/04/03# Or AssignedLots.WeekEnding = #2010/03/27# Guess I would say don't worry about performance implications of this one unless it's a problem. Sounds like you're already on that same page. :-) |