CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2082824
user2082824

Reputation: 11

Getting the maximum value of balances from different employee ids

I have a problem in using SELECT statement I want to SELECT the Maximum Balances Per Employee ID. I have a table like this.

EMPLOYE ID |  BALANCES
  c50-5050   |    100
  c50-5050   |    200
**c50-5050   |    300**
  c50-5051   |    200
**c50-5051   |    300**

The one with the asterisk indicates what I want to SELECT from the table.

I actually need this to show in a datagrid view in VB.net. Do you have an idea on how to do this?

Upvotes: 1

Views: 92

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460288

You can use ROW_NUMBER with OVER-clause:

WITH X AS
(
   SELECT [EMPLOYE ID], BALANCES,
           RN = ROW_NUMBER() OVER (PARTITION BY [EMPLOYE ID] ORDER BY BALANCES DESC)
   FROM dbo.Table
)
SELECT EMPLOYE ID, BALANCES
FROM X
WHERE RN = 1

I actually need this to show in a datagrid view in VB.net. Do you have an idea on how to do this?

Use ADO.NET, you could use a DataAdapter to fill a DataTable:

Dim table = New DataTable()
Dim sql = "see sql above"
Using con = New SqlConnection(yourConnectionString)
    Using da = New SqlDataAdapter(sql, con)
       da.Fill(table)
    End Using
End Using
DataGridView1.DataSource = table

Upvotes: 2

polkduran
polkduran

Reputation: 2551

A max() group by query should do it

select max(balance), employe_id
from yourTable
group by employe_id

Upvotes: 1

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

use below query 

select max(Balances),employe_id from table
group by employe_id

Upvotes: 3

Madhav
Madhav

Reputation: 2415

Try the below code.

SELECT EMPLOYEE_ID,MAX(balances) FROM TABLE_NAME
GROUP BY EMPLOYEE_ID;

Upvotes: 1

Related Questions