Page 1 of 1

Select last row from table for every customer

Posted: Fri Jan 29, 2010 1:54 am
by shafiq2626
Hi!
I am making inventory system online software.
in customerpayment table there are multi records for every customer. but i want to select last record for those customer who has arrear(Balance) like table below

Code: Select all

pmtid | cust_id |       date     |debit | credit | balance
 1            4        10-01-2010    500     0         500
 2            5        11-01-2010    700     0         700
 3            4        11-01-2010    600     0         1100
 4            5        12-01-2010    500     0         1200
Now i want to select last record for cust id 4 and 5 and so on.
please help me with thanks
thanking you

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 3:23 am
by aravona
Do you mean something like?

Code: Select all

SELECT Balance FROM customerpayment WHERE cust_id = 4;
Or do you mean the latest date recorded for that person?

Code: Select all

SELECT Balance FROM customerpayment WHERE cust_id = 4 AND date = '11-01-2010';

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 3:39 am
by shafiq2626
its ok thanks for reply.
but my problem is not solved throug this matter because i want select multi records at one time query and should be display one by one even there will be thousend record.
ok do you understand my problem.
please response.
thanking you

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 3:43 am
by aravona
Not really understanding because you said you wanted the 'last record'

So do you want the latest record for 1 user. Or do you want all records for 1 user? Or are you wanting the latest record for ALL users? can you be clearer?

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 3:58 am
by shafiq2626
thanks
i want latest records for every user.
please response with thanks

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 4:02 am
by aravona
well this will do what you want if you manually put in the latest date.

Code: Select all

SELECT * FROM customerpayment WHERE date = '12-01-2010'
But I'm assuming you're using PHP to draw these onto a webpage?

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 4:11 am
by shafiq2626
yes i am doing it in php for webpage.
not against with a specifice date this should be display in a specific period like between 01-01-2010 and 20-01-2010.
i am using this query.

Code: Select all

$data=mysql_query("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'  order by cpm.date DESC,cpm.customerid DESC");
but this is displaying multi records agains all user if they hava multi record in table.

please check it with thanks

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 4:14 am
by aravona
shafiq2626 wrote:yes i am doing it in php for webpage.
not against with a specifice date this should be display in a specific period like between 01-01-2010 and 20-01-2010.
i am using this query.

Code: Select all

$data=mysql_query("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'  order by cpm.date DESC,cpm.customerid DESC");
but this is displaying multi records agains all user if they hava multi record in table.

please check it with thanks
If you dont want multiple records of 1 user you'll need to use the DISTINCT syntax http://www.w3schools.com/SQl/sql_distinct.asp

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 4:21 am
by shafiq2626
you can see in my above code that i am using distinct but not effeciting.

Re: Select last row from table for every customer

Posted: Fri Jan 29, 2010 4:29 am
by shafiq2626
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 last row from table for every customer

Posted: Fri Jan 29, 2010 4:39 am
by aravona
I think it maybe to do with your WHERE clause - how are you choosing those date variables?