Page 1 of 1

select update record from table

Posted: Fri Jan 29, 2010 5:37 am
by shafiq2626
Hi
i want to select update record for multi user please check this query
following query is working.

Code: Select all

select DISTINCT cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno,cpm.balance from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate' group by cpm.customerid order by cpm.date DESC"
but this is displaying frist record against every user not last can you check please.

Re: select update record from table

Posted: Fri Jan 29, 2010 8:27 am
by JakeJ
Since I have no idea what your data set looks like, I can't help you. If you're looking for the last of a group of records, try max() on whichever field you think is appropriate.

Re: select update record from table

Posted: Sat Jan 30, 2010 1:54 am
by shafiq2626
Hi!
you can say that one user has multi record date by date and other user also same conditon.
i want to select last record against every user.
my query is selcting first record for every user.
i want to select them order by date desc

Code: Select all

"select DISTINCT cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno,cpm.balance from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate' group by cpm.customerid order by cpm.date DESC"
please some one can check

Re: select update record from table

Posted: Sat Jan 30, 2010 5:30 am
by aneesme
order by cpm.customerid, cpm.date DESC

Please help to select last record against a cusotmer

Posted: Thu Feb 04, 2010 12:14 am
by shafiq2626
Hi!
i am using following query

Code: Select all

"select  cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno,cpm.balance from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate'  order by cpm.customerid,cpm.date DESC "
output you can see in attachment.

there are multple records agains one customer . Actualy i want to select his last record as you can see Muhammad shafique has two records i want to take his only top record balnce like 2000
and Azam Shmai has multiple records i want to display only his top record balance like 3200.
please any one check and help with thanks.

Re: select update record from table

Posted: Thu Feb 04, 2010 12:27 pm
by JakeJ
Try this:

"select cpm.customerid,cpm.date,cpm.debet,cpm.credet,cpm.invoiceno, MAX(cpm.balance) from customerpaymentsheet cpm where cpm.balance > '0' and cpm.date BETWEEN '$sdate' and '$edate' GROUP BY cpm.customername order by cpm.customerid, cpm.date DESC "

Notice the MAX(balance) and GROUP BY statements.