Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2006
    Posts
    4

    Unanswered: Selecting top 3rd of a recordset

    How do I select the top 3rd of a recordset? Doing "SELECT TOP 33 PERCENT" may omit records from the top 3rd (since 1/3 = 0.33333... = 33.333333...%), and doing "SELECT TOP 34 PERCENT" selects too many.

    I want to avoid using macros, and do a pure SQL solution (so that the query can be opened up in the Query View window. And no, I can't migrate to another database system.

    Thanks!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Have no idea off the top of my head, but am most curious! Why do you need to pull only the top 1/3 of records so precisely?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Also, are we talking about only viewing in the Query View Window, or in a form as well, and if in a form, Single View or Contiuous?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This is not particularly helpful but, say you have a result set of 4 records ... WHAT would be the TOP 3rd? The 1st record? The 1st 2 records (nope. that would be the 1st half ...)? Basically how do you propose to handle that Nth+1 record?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I agree with Mike - there are much greater error variances than the 1% you've identified.

    However - if it really is that crucial to be so accurate - I would probably get a count of the full query return, work out 1/3 of that and return a literal number of records with TOP (rather than a percentage) using dynamic SQL. You then have to start dealing with ties too of course...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2006
    Posts
    4
    We need the top 1/3 to be precise because it's for some business logic.

    Basically, let's say I have 306 rows. If I do "SELECT TOP 33 PERCENT" in Access, it will return 101 rows. Yet, if you calculate 306 divided by 3, you get 102.

    That one row missed could be a crucial data point in a smaller data set, and we don't want to exclude it.

  7. #7
    Join Date
    Jul 2006
    Posts
    4
    M Owen:

    Well, if there were 4 records, 4/3 = 1.3333..., which would be rounded down to 1. The 2nd third would be 4 * (2/3) = 2.6666..., which would be rounded up to 3. But, we're never going to have a recordset with only 4 records...it would be more like 400 records.


    pootle flump:

    Someone on another forum mentioned that to me too...but all attempts at creating a working query have failed. I did come close once, got it to select only the top third, but when I went to pass in a query instead of a table, the query didn't work anymore.

    And in case anyone asks, no...I can't provide the Access database, because it contains confidential data.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by HTPC2Good4U
    Someone on another forum mentioned that to me too...but all attempts at creating a working query have failed. I did come close once, got it to select only the top third, but when I went to pass in a query instead of a table, the query didn't work anymore.
    Nah - easy peasy. Can you post your SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by HTPC2Good4U
    M Owen:

    Well, if there were 4 records, 4/3 = 1.3333..., which would be rounded down to 1. The 2nd third would be 4 * (2/3) = 2.6666..., which would be rounded up to 3. But, we're never going to have a recordset with only 4 records...it would be more like 400 records.


    pootle flump:

    Someone on another forum mentioned that to me too...but all attempts at creating a working query have failed. I did come close once, got it to select only the top third, but when I went to pass in a query instead of a table, the query didn't work anymore.

    And in case anyone asks, no...I can't provide the Access database, because it contains confidential data.
    I wasn't saying you would ... 4 recs is to make a point of those left over stragglers ... And likewise that partial record may be the critical data point that you're arbitrarily dropping (or adding) that makes your business case ...

    As to Poots comment: SELECT COUNT(*) FROM WhateverTable;

    Get the # of records.
    OneThird=CountOfRecords / 3

    SELECT TOP " & OneThird & " FROM WhateverTable;
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Jul 2006
    Posts
    4
    Here is the query I was using to select the top third (the column/table names are different in my database from those in this example). It works if you are selecting from a table:

    SELECT *
    FROM (SELECT (SELECT count(*) FROM [my data table] AS x WHERE x.[unique column]<= y.[unique column]) AS
    RowId, * FROM [my data table] AS y)
    WHERE RowId < ((SELECT count(*) FROM [my data table])/3);


    But, where this query falls apart is when I use the following query to replace [my data table] (it finds matching rows between two tables):

    SELECT [table1].Rank, [table2].Rank, [table2].Company, [table2].ProductName
    FROM [table1] INNER JOIN [table2] ON ([table1].Company = [table2].Company) AND ([table1].ProductName = [table2].ProductName);


    I'm heading out of the office for today...so I will not be able to reply to any further comments until tomorrow morning.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Sorry - you caught me leaving the office and I forgot. The first query will fail if you ever get gaps in your autonumbers. If you say you never will then there is probably a design flaw. Teddy and Mike will lecture you about surrogate keys and their lack of meaning

    I will have another look tomoz if you are still struggling. Busy at mo....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My stab at this:
    Code:
    Sub SelectTopThird()
     
        Const COUNT_SQL As String = "SELECT COUNT(*) " & _
                                    "FROM [Table1a] INNER JOIN [Table2a] ON ([Table1a].Company = [Table2a].Company) AND ([Table1a].ProductName = [Table2a].ProductName)"
        Const QDEF_SQL  As String = "SELECT TOP @Replace [Table1a].Rank, [Table2a].Rank, [Table2a].Company, [Table2a].ProductName " & _
                                    "FROM [Table1a] INNER JOIN [Table2a] ON ([Table1a].Company = [Table2a].Company) AND ([Table1a].ProductName = [Table2a].ProductName)"
        Const QDEF_NAME As String = "MyTopQuery"
     
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Dim QDef As DAO.QueryDef
        Dim TheNumber As Integer
     
        Set db = CurrentDb
     
        Set rst = db.OpenRecordset(COUNT_SQL, dbOpenForwardOnly, dbReadOnly)
     
        If rst.RecordCount > 0 Then
            TheNumber = Round(rst.Fields(0).Value / 3)
        Else
            'you have a problem
        End If
     
        Set QDef = db.QueryDefs(QDEF_NAME)
     
        QDef.SQL = Replace(QDEF_SQL, "@Replace", TheNumber)
     
        DoCmd.OpenQuery QDef.name
     
    On Error Resume Next
     
        rst.Close
        Set rst = Nothing
        db.Close
        Set db = Nothing
        Set QDef = Nothing
     
    End Sub
    Note - I named my tables Table1(2)a. Also I created a query called MyTopQuery.

    Mike might do more or less the same but use ADO\ ADOX (although he is finding DAO rather sturdy at the moment I believe I coudn't get it to break either Mike.)

    BTW - there is a bit of a gotcha for ties when using TOP. Also - are you certain you don't need an order by clause?

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Oct 2005
    Posts
    7
    I don't know SQL, so I ask, have you considered using the Top Values box in design view? You can type 33% in there, press Enter and be done with it. (Be sure to sort your records in acsending or descending order.) Then you can look at your SQL if you want.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by djl0525
    I don't know SQL, so I ask, have you considered using the Top Values box in design view? You can type 33% in there, press Enter and be done with it. (Be sure to sort your records in acsending or descending order.) Then you can look at your SQL if you want.
    Hi

    The box is just a GUI to prevent you having to type SELECT TOP 33 PERCENT - it just writes this for you behind the scenes. So - the two are one and the same.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •