Select max value from each group and insert into another table in SQL Server

Last Reply 16 days ago By dharmendr

Posted 16 days ago

Hello Community,

i need your help.

How do i insert rows from table1 into table2 where row points is max grouped by name?

Table1

NAME FIRSTNAME POINTS
Alpha Alphazy 10
Alpha Alphazy 12
Beta Betazy 9
Beta Betazy 15

Result after SQL QUERY i want

Table2

NAME FIRSTNAME POINTS
Alpha Alphazy 12
Beta Betazy 15

i use : 

Dim sql1 as string = "INSERT INTO Table2 SELECT [Table1].NAME, [Table1].FIRSTNAME
FROM Table1 LEFT JOIN Table2 ON [Table1].[NAME] = [Table2 ].[NAME] AND [Table1].[FIRSTNAME] = [Table2 ].[FIRSTNAME]
WHERE (([Table2 ].[NAME]) Is Null) AND (([Table1].POINTS) > 10)"

(([Table1].POINTS) > 10) give me the results > 10

i want the MAX results of [Table1].POINTS

Thanks by advance

Posted 16 days ago

Hi ryutenkan,

Refer the below query and write in the VB.Net code.

Or create a stored procedure and call the stored procedure in code.

For calling stored procedure refer below article.

Select SQL Server Stored Procedures using ASP.Net Example

SQL

CREATE Table1(NAME VARCHAR(50),FIRSTNAME VARCHAR(50),POINTS INT)
INSERT INTO Table1 VALUES('Alpha','Alphazy',10)
INSERT INTO Table1 VALUES('Alpha','Alphazy',12)
INSERT INTO Table1 VALUES('Beta','Betazy',9)
INSERT INTO Table1 VALUES('Beta','Betazy',15)

CREATE Table2(NAME VARCHAR(50),FIRSTNAME VARCHAR(50),POINTS INT)
INSERT INTO Table2
SELECT NAME,FIRSTNAME, MAX(POINTS) POINTS
FROM TABLE1
GROUP BY NAME,FIRSTNAME

SELECT * FROM Table2

Output

NAME FIRSTNAME POINTS
Alpha Alphazy 12
Beta Betazy 15