CODEWITHSUNDEEP
sqlsqlitegreatest-n-per-group
Ruwani Sangeeths
Ruwani Sangeeths

Reputation:

Select the maximum date from sql table using java

try {

    String sql = "SELECT MAX(date),empNo,basicAmt,increment FROM Basic_sal where empNo=?";
    pst = conn.prepareStatement(sql);
    String empNO = com_emp.getSelectedItem().toString();
    pst.setString(1, empNO);
    rs = pst.executeQuery();

    if (rs.next()) {
        //JOptionPane.showMessageDialog(null,"Success !!!" );
        float a = rs.getFloat("basicAmt");
        String b = Float.toString(a);
        txt_basicsalary.setText(b);

I used this code part in Netbeans to take the maximum date from my basic salary table. but the output gives the most recently added record's date, but actually what I want is the maximum date according to the empNo. But the query gives the maximum date in SQLiteManager. It would be appreciated if someone could provide the answer soon. I'm using SQLiteManager.

@ Mureinik.....Yes I need the all the fields in the code

Upvotes: 0

Views: 307

Answers (1)

JNevill
JNevill

Reputation: 50218

You want to select the record for each employee that has the max(date) for that employee. To do that your best off using a correlated subquery (Regardless of the RDBMS... with maybe some exceptions)

 SELECT date,empNo,basicAmt,increment FROM Basic_sal bs where empNo=? AND date = (SELECT max(date) FROM basic_sal WHERE empNo = bs.empNo);

Here that subquery is grabbing the max date for the employee of that specific record and using that as the filter in the WHERE clause.

Upvotes: 1

Related Questions