sql command show highest count for year 2000-CodePudding

I have a table (actor) in the database(movie)

nationality	gender	year	actor	count
USA	male	2005	Smith	6
USA	male	2005	Jones	8
USA	male	2005	Edwards	8
USA	male	2005	Rubio	10
Canada	male	2005	Thompson	6
Canada	male	2005	Watson	8
Australia	male	2005	Hopkins	8
Australia	male	2005	Howard	10
UK	male	2005	Harper	8
UK	male	2005	Palmer	7
UK	male	2005	Robinson	10
USA	female	2005	Love	9
USA	female	2005	Jonas	6
USA	female	2005	Nilson	8
USA	female	2005	Cruz	11
Canada	female	2005	Davidson	7
Canada	female	2005	Del Ante	9
Australia	female	2005	Hope	11
Australia	female	2005	Capa	12
UK	female	2005	Bernard	22
UK	female	2005	Freeman	23
UK	female	2005	Simson	10
USA	male	2015	Mitchell	6
USA	male	2015	Jona	8
USA	male	2015	Edwards	8
USA	male	2015	Rubio	11
Canada	male	2015	Troyson	6
Canada	male	2015	Watson	8
Australia	male	2015	Hopkins	8
Australia	male	2015	Howard	10
UK	male	2015	Harper	8
UK	male	2015	Palmer	7
UK	male	2015	Robinson	10
USA	female	2015	Love	9
USA	female	2015	Jonas	6
USA	female	2015	Nilson	8
USA	female	2015	Cruz	11
Canada	female	2015	Davidson	7
Canada	female	2015	Del Ante	9
Australia	female	2015	Hope	11
Australia	female	2015	Capa	12
UK	female	2015	Shaw	12
UK	female	2015	Freeman	23
UK	female	2015	Samuels	12

Now I would like to know which actor and actress of every country played most in the year 2005 in the order of counts in ascending order.

I am expecting these output:

nationality	gender	year	actor	count
UK	female	2005	Freeman	23
Australia	female	2005	Capa	12
USA	female	2005	Cruz	11
USA	male	2005	Rubio	10
Australia	male	2005	Howard	10
UK	male	2005	Robinson	10
Canada	female	2005	Del Ante	9
Canada	male	2005	Watson	8

I try these code, but got not the desired solution

SELECT nationality, gender, year,actor, max(count) as count from (actor) where year='2005'

CodePudding user response：

Try this

SELECT nationality, gender, year, actor, max(count) AS count
FROM actor
WHERE year = 2005
GROUP BY nationality, gender
ORDER BY count DESC