Sql query for order by problem

Question

Suppose i have a table with Column IdItemDates and info in Dates is different just like--- 01/12/2016 02/12/2016 29/11/2016 20/10/2016 28/11/2016 this type of date entry or different month now how to used order by Asc for update any item details from first date to last for particular item basically this is used for back date entry then i want update all records from first date to last date which match with item name ....

Technetshadow · Answer

You can try it using CTE like below: With cteUpdate As ( SELECT IdItem Date FROM Your_Table_Name ORDER BY Date ) UPDATE cteUpdate SET Item= 'Item Details'

Nitin Sontakke · Answer

Just as I expected. Dates stored in nvarchar column will be sorted as string so 21/10/2016 will be greater than 01/12/2016 while in reality it is less. This is because 0 is less than 2. You have two options either convert that nvarchar to date at runtime or change the column datatype (carefully! after making some safe copies). The second option is the best. Sorry - Too much code to read and comprehend. All I can say is you are too much coding too small a thing to achieve. Think of taking stored procedure approach to move all db related processing there.

Jitendra Sharma · Answer

string pqr1 = select * from General_Cash_Flow where General_Item=' + cmbxCltId.Text + ' and CrtDate=' + login.setvalueyear + ' order by Dates Asc; cmd = new SqlCommand(pqr1 cnn); dr = cmd.ExecuteReader(); while (dr.Read()) { string Id = dr[Id].ToString(); id11 = Convert.ToInt32(Id); string Dates = dr[Dates].ToString(); dat2 = Dates; string Credit = dr[Credit].ToString(); crt = Credit; string Debit = dr[Debit].ToString(); drt = Debit; } i have some dates like - 01/12/2016 20/11/2016 02/12/2016 28/11/2016 29/11/2016 now according to me date asc order start from 20/11/2016 but right now start from 01/12/2016 so i want to ask why ..... how to start from asc

Jitendra Sharma · Answer

if ((fday { int id1 = 0; cnn.Open(); string str9 = Select Max(Id) from General_Cash_Flow; cmd = new SqlCommand(str9 cnn); da = new SqlDataAdapter(cmd); dt = new DataTable(); da.Fill(dt); cmd.ExecuteReader(); if (dt.Rows.Count > 0) { id1 = Convert.ToInt32(dt.Rows[0][0].ToString()); } cnn.Close(); int cid = id1 + 1; string amt = ; string vtype = Recipt; cnn.Open(); string qry2 = insert into General_Cash_Flow(IdDatesGeneral_ItemDebitCreditThroughCheqNoTotalVoucherTypeCrtDate) values(' + cid + '' + dddt + '' + cmbxCltId.Text + '' + amt + '' + txtPaidAmount.Text + '' + cmbxBankName.Text + ' ' + txtCheckNumb.Text + '' + gentotal + '' + vtype + '' + login.setvalueyear + '); cmd = new SqlCommand(qry2 cnn); cmd.ExecuteNonQuery(); cnn.Close(); Refresh(); string GItem = string.Empty; string crt = string.Empty; string dat2 = string.Empty; string drt = string.Empty; float total; int id11; cnn.Open(); string pqr1 = select * from General_Cash_Flow where General_Item=' + cmbxCltId.Text + ' and CrtDate=' + login.setvalueyear + ' order by Dates Asc; cmd = new SqlCommand(pqr1 cnn); dr = cmd.ExecuteReader(); while (dr.Read()) { string Id = dr[Id].ToString(); id11 = Convert.ToInt32(Id); string Dates = dr[Dates].ToString(); dat2 = Dates; string Credit = dr[Credit].ToString(); crt = Credit; string Debit = dr[Debit].ToString(); drt = Debit; if (count == 1) { float Gtotal = openamount; cnn1.Open(); string sttr3 = update General_Cash_Flow set Total=' + Gtotal + ' where General_Item=' + cmbxCltId.Text + ' and Dates=' + dat2 + ' and Id=' + id11 + ' and CrtDate=' + login.setvalueyear + ' ; cmd1 = new SqlCommand(sttr3 cnn1); cmd1.ExecuteNonQuery(); cnn1.Close(); cnn1.Open(); string sttr4 = update Sale_Total set Ttlrcv=' + Gtotal + ' where General_Item=' + cmbxCltId.Text + ' and CrtDate=' + login.setvalueyear + ' ; cmd1 = new SqlCommand(sttr4 cnn1); cmd1.ExecuteNonQuery(); cnn1.Close(); Refresh(); count++; } else if (crt != ) { float salttl = 0; cnn1.Open(); string st1 = Select * from Sale_Total where General_Item =' + cmbxCltId.Text + ' and CrtDate=' + login.setvalueyear + '; cmd1 = new SqlCommand(st1 cnn1); da = new SqlDataAdapter(cmd1); dt = new DataTable(); da.Fill(dt); cmd1.ExecuteReader(); if (dt.Rows.Count > 0) { float Ttlrcv = float.Parse(dt.Rows[0][4].ToString()); salttl = Ttlrcv; } cnn1.Close(); float Gtotal3 = 0; Gtotal3 = float.Parse(crt) + salttl; cnn1.Open(); string sttr4 = update Sale_Total set Ttlrcv=' + Gtotal3 + ' where General_Item=' + cmbxCltId.Text + ' and CrtDate=' + login.setvalueyear + ' ; cmd1 = new SqlCommand(sttr4 cnn1); cmd1.ExecuteNonQuery(); cnn1.Close(); cnn1.Open(); string sttr = update General_Cash_Flow set Total=' + Gtotal3 + ' where General_Item=' + cmbxCltId.Text + ' and Dates=' + dat2 + ' and Id=' + id11 + ' and CrtDate=' + login.setvalueyear + ' ; cmd1 = new SqlCommand(sttr cnn1); cmd1.ExecuteNonQuery(); cnn1.Close(); Refresh(); } else if (drt != ) { float salttl = 0; cnn1.Open(); string st1 = Select * from Sale_Total where General_Item =' + cmbxCltId.Text + ' and CrtDate=' + login.setvalueyear + '; cmd1 = new SqlCommand(st1 cnn1); da = new SqlDataAdapter(cmd1); dt = new DataTable(); da.Fill(dt); cmd1.ExecuteReader(); if (dt.Rows.Count > 0) { float Ttlrcv = float.Parse(dt.Rows[0][4].ToString()); salttl = Ttlrcv; } cnn1.Close(); float Gtotal3 = 0; Gtotal3 = float.Parse(drt) - salttl; cnn1.Open(); string sttr4 = update Sale_Total set Ttlrcv=' + Gtotal3 + ' where General_Item=' + cmbxCltId.Text + ' and CrtDate=' + login.setvalueyear + ' ; cmd1 = new SqlCommand(sttr4 cnn1); cmd1.ExecuteNonQuery(); cnn1.Close(); cnn1.Open(); string sttr = update General_Cash_Flow set Total=' + Gtotal3 + ' where General_Item=' + cmbxCltId.Text + ' and Dates=' + dat2 + ' and Id=' + id11 + ' and CrtDate=' + login.setvalueyear + ' ; cmd1 = new SqlCommand(sttr cnn1); cmd1.ExecuteNonQuery(); cnn1.Close(); Refresh(); } Refresh(); } dr.Close(); cnn.Close(); here date is also nvarchar here i want to select data according to dates asc order one by one please check but this condition only apply for one month date but when date goes to last month then not working please help me..

Nitin Sontakke · Answer

The UPDATE and ORDER BY clauses are completed unrelated. You only mention whatever records you want to update in the WHERE clause of the update query. The database engine will take care of the rest. ORDER BY clause is applicable only to the SELECT clause because the order in which server will return the rows is unpredictable (especially when primary key is missing). I also suspect that in database you are having date column as a varchar and not date. Please clarify.