Pick top 5 from data (1 Viewer)

D

dassuz

New member

Local time

Today, 04:04

Joined

Jan 10, 2019

Messages

4

• Aug 29, 2019

• #1

Hi I have a table with hundreds of line of data which has been sorted by username and date1. I want to bring back the top 5 records for each username. What is the best way to do this in a query. Example data is the attachment with the way I would like the data to look once its complete

 

Attachments

• Top 5 Records Data.xlsx

  12.4 KB · Views: 58

Last edited: Aug 29, 2019

MajP

You've got your good things, and you've got mine.

Local time

Yesterday, 23:04

Joined

May 21, 2018

Messages

8,527

• Aug 29, 2019

• #2

Several methods explained here
https://en.wikibooks.org/wiki/Structured_Query_Language/Retrieve_Top_N_Rows_per_Group

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 20:04

Joined

Oct 29, 2018

Messages

21,471

• Aug 29, 2019

• #3

Hi. Welcome to AWF! Here's one more link for the same thing.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 11:04

Joined

May 7, 2009

Messages

19,238

• Aug 29, 2019

• #4

is that that field from a query?

use this query against that query:

Code:Copy to clipboard

select queryName.* from queryName 
where  
     (select count("1") from queryName as t1 
           where t1.username=queryName.username and 
           t1.mydatefield>= queryName.mydatefield) < 6;