By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,663 Members | 1,813 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,663 IT Pros & Developers. It's quick & easy.

Sql select question

P: 1
This in Access 2003. Pardon me if this is in the wrong spot, but I hoped someone could help.

I have a table with multiple records that have a text represented date field. The date is in a format like: "06/01/2009" I need to select all the fields from the table but only the 6 oldest rows that fall in a range for each group of "DocType, PayTo, ContactName, ContactNumber, DocFooter, PQBName, LetterDate, RetireeFirstName, RetireeLastName, Address1, Address2, City, State, ZIP, PQBSSN, EmployerCode" ordered by the DateDue.

Like: Select only records with a date range 01/01/2009 - 12/01/2009, and of those only select the 6 oldest entries for each grouping.

I have monkeyed with this for a bit and am having no luck. I know this is pretty basic, but I just cant seem to make this work. Here is the SQL select I use to get the date from the table now.
Expand|Select|Wrap|Line Numbers
  1. SELECT c.DocType
  2.      , c.PayTo
  3.      , c.ContactName
  4.      , c.ContactNumber
  5.      , c.DocFooter
  6.      , c.PQBName
  7.      , c.LetterDate
  8.      , c.RetireeFirstName
  9.      , c.RetireeLastName
  10.      , c.Address1
  11.      , c.Address2
  12.      , c.City
  13.      , c.State
  14.      , c.ZIP
  15.      , c.PQBSSN
  16.      , c.EmployerCode
  17.      , c.AmountDue
  18.      , c.DateDue
  19.      , Right(c.[DateDue],4)+Left(c.[DateDue],2)+Mid(c.[DateDue],4,2) AS SORTDATE
  20.   FROM COUPONS c
  21.  ORDER
  22.     BY c.DocType
  23.      , c.PayTo
  24.      , c.ContactName
  25.      , c.ContactNumber
  26.      , c.DocFooter
  27.      , c.PQBName
  28.      , c.LetterDate
  29.      , Right(c.[DateDue],4)+Left(c.[DateDue],2)+Mid(c.[DateDue],4,2)
To restate the problem: the query is returning too many rows, I ONLY want six rows for each 'group' of distinct values for the seven columns in the order by...

Any help would be very appreciated!
Jun 10 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 2,653
Hello, scalda.

Do you have PK field in your table or any unique field or fields combination?

P.S. I'm quite impressed with creativity of your approach towards making ordering criteria from text date. However, the same could be achieved with CDate() function since your field is very suitably formatted.
Jun 10 '09 #2

Expert 100+
P: 378
Here's a simple example of what you need:

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 6 [your fields]
  2. FROM [your tables]
  3. ORDER BY [SortDate];
Jun 10 '09 #3

Expert Mod 15k+
P: 31,712
Hi Scalda. Welcome to Bytes!

I'm not sure why you see this as a simple problem. It is not remotely so. I suspect I can produce something using Subqueries in SQL, but I will need to do some checking first of the underlying concepts.

Congratulations on posting your SQL so readably by the way. Not everyone has the sense to do that. Noticed and appreciated :)

Please remember for the future though, to use the [ CODE ] tags.

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).
Jun 12 '09 #4

Expert Mod 15k+
P: 31,712
I tried, but I can't find a way to do this in SQL (so much for simple).

The TOP predicate waorks over the whole SELECT query it pertains to. Linking in a subquery, which itself uses TOP, fails as there is no way to tell it to rerun for every value(s) that you want to GROUP BY. Sorry.
Jun 12 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.