Results 1 to 15 of 15

How can i select top 3 record set ( i mean 3 rows ) from each group without duplications

  1. #1
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41

    How can i select top 3 record set ( i mean 3 rows ) from each group without duplications

    Hi there.... This is my first topic here... And i need help.... My query have uniq key but there is dipplicates on each group ... Like on the area when i typi my condition ( select top 3 area from yealsland...... So on ) the result will be dipplicates on area

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,308
    Try Google with this search term "select top 3 from group"

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,266
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    Thanks alot , but i have the group dipplicates values Click image for larger version. 

Name:	ImageUploadedByTapatalk1421051751.610273.jpg 
Views:	15 
Size:	95.9 KB 
ID:	19302 can you see the code in the picture ... Thanks for help

  5. #5
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41

    How can i select top 3 record set ( i mean 3 rows ) from each group without ...

    I have 6 groups on this query i need to display 3 first records on each group

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,308
    Did you try googling as I suggested in post #2?
    You may find this helpful.
    Good luck.

  7. #7
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    I try google search and i find many web sits uses top n , but that not help

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,266
    We have given guidance for how. Why doesn't one of them work?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,308
    I try google search and i find many web sits uses top n , but that not help
    Well that doesn't really help. You have been given links to examples and tutorials.
    Show us the data, and the code you have used; and the result you obtained.

    My last link has 3 ways of finding top 5 in a recordset. Which ones did you try?

    You're going to have to show us something if we are going to help you.

  10. #10
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    Thanks for help i take 2 pictures the code and the results Click image for larger version. 

Name:	ImageUploadedByTapatalk1421179938.205387.jpg 
Views:	12 
Size:	95.9 KB 
ID:	19321Click image for larger version. 

Name:	ImageUploadedByTapatalk1421179950.573255.jpg 
Views:	12 
Size:	241.9 KB 
ID:	19322

  11. #11
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    Oh,,, sory it's not clear i will write it

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,266
    Can copy/paste the SQL statement into post.

    Try doing Screen Print instead of photo.

    Provide sample of raw data.

    Provide translation of field names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    Select distinct mahsol.Year, mahsol. Session, mahsol.village, mahsol.seq_no, exc_sample.name_mahsolmain, owner details.ownname
    From ( owner_details inner join owner on (owner_details. Own_id= owner.seq_no) and (owner_details.own_village= owner.village) ) inner join (mahsol inner join exc_sample on (mahsol.seq_no= exc_sample.seq_no) and ( mahsol.village =exc_sample.billage) and ( mahsol.session=exc_sample.session) and ( mahsol.year= exc_sample.year) and ( mahsol.main_mahsol=exc_sample.main_mahsol) ) on ( owner.seq_no= mahsol.seq_no) and( owner.village=mahsol.village) and (owner.session=mahsol.session) and (owner.year=mahsol.year)

    Where((( mahsol. Not involved _mahsol)=1))
    Group by mahsol.year, mahsol.session, mahsol.village, mahsol.seq_no, exc_sample.name_mahsolmain, owner details.own_name, mahsol.mainmahsol
    Having ((( mahsol.seq_no) in ( select top 3 percent mahsol. Seq_no from mahsol)))
    Order by mahsol.main_mahsol;

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,308
    from Allen Browne's site
    TOP n records per group

    You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:
    SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
    FROM Orders
    WHERE Orders.OrderID IN
    (SELECT TOP 3 OrderID
    FROM Orders AS Dupe
    WHERE Dupe.CustomerID = Orders.CustomerID
    ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
    ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;
    Points to note:

    • Since we have two copies of the same table, we need the alias.
    • Like EXISTS in the first example above, there is no problem with the subquery returning multiple records. The main query does not have to show any value from the subquery.
    • Adding the primary key field to the ORDER BY clause differentiates between tied values.

  15. #15
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    Thank you orange

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Counting Specific Duplications in a Single Table
    By alpinegroove in forum Queries
    Replies: 13
    Last Post: 08-19-2014, 11:56 PM
  2. Group Text Box to output in columns and not rows
    By Fuzz_cozens in forum Reports
    Replies: 1
    Last Post: 02-28-2014, 10:30 AM
  3. GROUP by yet SELECT fields not within GROUP BY
    By johnseito in forum Access
    Replies: 25
    Last Post: 11-03-2013, 10:20 PM
  4. Replies: 7
    Last Post: 06-12-2013, 06:36 AM
  5. Percentages in reports for group rows
    By John Donovan in forum Reports
    Replies: 1
    Last Post: 12-28-2010, 09:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums