0

I have a count of a top 2

My table has this data

Name Age price visited     size
Jon  34  53    2018-01-01  9  
Don  22  70    2018-03-01  15
Pete 76  12    2018-11-09  7
Jon  34  55    2018-09-13  9
Paul 90  64    2018-07-08  6
Pete 76  31    2018-03-25  7
Jon  75  34    2018-06-06  8


select top 2
name,
count(name) as cnt
from
tbl1
group by name
order by cnt desc

Which returns my top 2 names

Jon  3
Pete 2

This name will change dynamically as the query is run depending on who has made the most visits in total (this is very simplified the actual table has 1000's of entries).

What I would like to do is then use the result of that query to get the following all of which needs to be in a single query;

Name Age price visited     size
Jon  34  53    2018-01-01  9  
Jon  34  55    2018-09-13  9
Jon  75  34    2018-06-06  8
Pete 76  12    2018-11-09  7
Pete 76  31    2018-03-25  7

In summary, count who has visited the most and then display all the records under those names.

Thanks in advance

dwir182
  • 1,539
  • 10
  • 20
Michael
  • 63
  • 7
  • Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – Clockwork-Muse Nov 07 '18 at 00:22

1 Answers1

2

Here's one option using in:

select *
from yourtable
where name in (
    select top 2 name
    from yourtable
    group by name
    order by count(*) desc
)
order by name
sgeddes
  • 62,311
  • 6
  • 61
  • 83