Select last row from table for every customer

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Select last row from table for every customer

Post 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
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Select last row from table for every customer

Post 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';
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Re: Select last row from table for every customer

Post 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
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Select last row from table for every customer

Post 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?
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Re: Select last row from table for every customer

Post by shafiq2626 »

thanks
i want latest records for every user.
please response with thanks
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Select last row from table for every customer

Post 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?
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Re: Select last row from table for every customer

Post 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
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Select last row from table for every customer

Post 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
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Re: Select last row from table for every customer

Post by shafiq2626 »

you can see in my above code that i am using distinct but not effeciting.
shafiq2626
Forum Commoner
Posts: 88
Joined: Wed Mar 04, 2009 1:54 am
Location: Lahore
Contact:

Re: Select last row from table for every customer

Post 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.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Select last row from table for every customer

Post by aravona »

I think it maybe to do with your WHERE clause - how are you choosing those date variables?
Post Reply