Click here to Skip to main content
15,354,956 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a table with fields name, month, score such as below

name     month      score
john     April        12
john     Jun          9
john     may          12
mary     may          3
mary     august       5
bob       may         25



i want output of query be the name and month and maximum score of each person.
john has two maximum record score 12

so output will be

john    april    12
john    may      12
mary    may       5
bob     may      25


What I have tried:

SQL
select name.month, max(score) from test.db
group by name
Posted
Updated 15-Mar-22 23:02pm
v2
Comments
_Asif_ 16-Mar-22 10:01am
   
Should not the output for mary be like
mary august 5 instead of mary may 5?

I have a solution, however I'm not sure at all it is the simplest approach:

List<Item> items=new List<Item>();
items.Add(new Item("john","april",12));
items.Add(new Item("john", "june", 9));
items.Add(new Item("john","may",12));
items.Add(new Item("mary","may",3));
items.Add(new Item("mary","august",5));
items.Add(new Item("bob","may",25));
var someItems = items.Where(x => x.Score == items.Where(z => z.Name == x.Name)
                                            .OrderByDescending(y => y.Score)
                                            .First().Score);
foreach (var item in someItems) {
    Console.WriteLine(item.ToString());
}


with

public class Item {
    public string Name;
    public string Month;
    public int Score;
    public Item(string name, string month, int score) {
        this.Name = name;
        this.Month = month;
        this.Score = score;
    }
    public override string ToString() {
        return Name + "," + Month + ","+Score;
    }
}


ADDED:
Order-then-first makes no sense, better is:

var someItems = items.Where(x => x.Score == items.Where(z => z.Name == x.Name)
                                            .Max(y => y.Score));


ADDED2: I am aware this isn't SQL; however you can use LINQ on databases or you can apply the same approach yourself in SQL (Two nested SELECT statements).

ADDED3: mysql - SQL select only rows with max value on a column - Stack Overflow[^] shows you two ways to do it in SQL.
   
v7
For SQL Server, the RANK function[^] is probably the simplest option:
SQL
DECLARE @T TABLE 
(
    [name] varchar(10) NOT NULL, 
    [month] varchar(9) NOT NULL, 
    [score] int NOT NULL
);

INSERT INTO @T ([name], [month], [score])
VALUES
	('john', 'April', 12),
	('john', 'Jun', 9),
	('john', 'may', 12),
	('mary', 'may', 3),
	('mary', 'august', 5),
	('bob', 'may', 25)
;

WITH cte As
(
	SELECT
		[name],
		[month],
		[score],
		RANK() OVER (PARTITION BY [name] ORDER BY [score] DESC) As RN
	FROM
		@T
)
SELECT
	[name],
	[month],
	[score]
FROM 
	cte
WHERE
	RN = 1
;
Output:
| name | month  | score |
|------|--------|-------|
| bob  | may    | 25    |
| john | April  | 12    |
| john | may    | 12    |
| mary | august | 5     |
   
Comments
_Asif_ 16-Mar-22 9:59am
   
Output does not match with OPs desired Output
Richard Deeming 16-Mar-22 10:02am
   
The only difference is the order. And since there is no ordering defined in the OP's query, and no column by which to order the results to match the example in the question, that's irrelevant.

The actual data is a precise match for the OP's desired output.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900