Click here to Skip to main content
15,917,481 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
sql queries problem

--------------------
i have one tabel with column name
date,client_name,deposit_amount

----------------table overview----------
date | client_name | deposit_amount
-------- ------------ -------------
17/06/2010 abc 1500
18/06/2010 abc 500
19/06/2010 abc 0.00
17/06/2010 xyz 1000
18/06/2010 xyz 500
16/06/2010 mnp 2000
19/06/2010 mnp 500
---------------------------------------
how to fetch client_name with max date and deposit_amount>0

if true then fine a record like this.....

----------------table overview----------
date | client_name | deposit_amount
-------- ------------ -------------

18/06/2010 xyz 500
19/06/2010 mnp 500
--------------------------------------

how to find this type of result with use of sql queries..
Posted

SELECT * FROM MYTABLE AS A
WHERE [DATE] = (
 SELECT MAX([DATE]) FROM MYTABLE 
 WHERE MYTABLE.client_name =  A.client_name AND deposit_amount > 0
      )
 
Share this answer
 
Comments
nilong 19-Jun-10 3:00am    
no.this nor proper result ..i want get only greater than 0.00 deposit_amount
deposit_amount > 0...
but i get result...
thnx for support
i get the solution.....
i have try this queries...

SELECT date,clientname,remain_amount
FROM <tablename>WHERE(date IN(SELECT MAX(date) AS Expr1 FROM <tablename> AS <tablename>_1
WHERE (remain_amount &gt; 0)
GROUP BY clientname)) AND (no IN
(SELECT MAX(no) AS Expr1
FROM <tablename> AS <tablename>_1
GROUP BY clientname)) AND (remain_amount > 0)

---------------------------------------i get result ---
----------------table overview----------
date | client_name | deposit_amount
-------- ------------ -------------
18/06/2010 xyz 500
19/06/2010 mnp 500
-------------------------------------------

*no -- field are use in table 1'st field foe auto increment every entry in my table

njoy life...
bye
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900