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?