how do get last record from sql

i have web form and data is inserted properly.against one employee i have multiple records in the table.

 EmployeeStatus empStatus = new EmployeeStatus();           
      empStatus.Employee_Id = Employee.Id;   
              empStatus.EmployeeStatusType_Id = SafeConvert.ToByte(ddlEmpStatus.SelectedValue);  
               empStatus.StartDate = SafeConvert.ToDateTime(txtStartDate.Text);
 empStatus.EndDate = SafeConvert.ToDateTime(txtEndDate.Text);         
        empStatus.Sector_Id = SafeConvert.ToByte(ddlSelectSector.SelectedValue);    
             empStatus.Rank_Id = SafeConvert.ToByte(ddlRank.SelectedValue);   
              empStatus.IsDutiable = txtIsDutiable.Checked;     
            empStatus.HasWirles = txtHasWirles.Checked;     
            empStatus.DutyType_Id = SafeConvert.ToByte(ddlDutyType.SelectedValue);     
            empStatus.EmployeeShift_Id = SafeConvert.ToByte(ddlEmployeeShift.SelectedValue);
 _service.InsertEmployeeStatus(empStatus);      
           _queryStatus = empStatus.Id > 0;
i have tried all last&default method,orderby,etc but result is  getting properly.because against one employee there are different status  and different duty type, so i got one idea that i add a new column in  that table which is a bit type,and when i insert new record against that  employee bit is set true for that last record and for previous records  is set false>>how that cab be done using c#.

I do not know what you are doing with the code you have posted but if you want the last record in an sql database that meets the selection criteri you would have an sql statement somewhere that resemles this for example.

SELECT * FROM empStatus WHERE status = (the status your are interested in) [AND [field = ?] ] ORDER BY [database record id] DESC LIMIT 1

The sql you would send to your database engine would be

SELECT * FROM empStatus WHERE status = 2 AND sector_id = 4 AND startdate = '2009/03/25' AND haswireless = 1 AND ...

this would give you the last record in the database that matches the selection criteria.

All this is assuming that you have a record id field with auto increment set in your database table.

Others here may have a better idea based on a better understanding of the example code in your post.

It would be good to know what SQL database engine you are using, MS SQL, MySQL, SQLite, Access because each may have a different way of getting the last record from it's tables.

Another thing if you are providing values for every field as you have done in your example, you may not get any record back because of the tight restrictions you are giving the database engine, in other words there may not be a record in the database that matches your criteria to get from the database table.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

theLizard11y ago
I do not know what you are doing with the code you have posted but if you want the last record in an sql database that meets the selection criteri you would have an sql statement somewhere that resemles this for example.

SELECT * FROM empStatus WHERE status = (the status your are interested in) [AND [field = ?] ] ORDER BY [database record id] DESC LIMIT 1

The sql you would send to your database engine would be

SELECT * FROM empStatus WHERE status = 2 AND sector_id = 4 AND startdate = '2009/03/25' AND haswireless = 1 AND ...

this would give you the last record in the database that matches the selection criteria.

All this is assuming that you have a record id field with auto increment set in your database table.

Others here may have a better idea based on a better understanding of the example code in your post.

It would be good to know what SQL database engine you are using, MS SQL, MySQL, SQLite, Access because each may have a different way of getting the last record from it's tables.

Another thing if you are providing values for every field as you have done in your example, you may not get any record back because of the tight restrictions you are giving the database engine, in other words there may not be a record in the database that matches your criteria to get from the database table.