Click here to Skip to main content
14,384,492 members
Rate this:
Please Sign up or sign in to vote.
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
Rate this:
Please Sign up or sign in to vote.

Solution 2

SELECT * FROM MYTABLE AS A
WHERE [DATE] = (
 SELECT MAX([DATE]) FROM MYTABLE 
 WHERE MYTABLE.client_name =  A.client_name AND deposit_amount > 0
      )
   
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100