Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
SQL
fbalance    fdate         facno
10,000.00  21/12/2013     010001801000007
15,000.00  21/01/2014     010001801000007
20,000.00  15/01/2014     010001801000007
29,550.00  21/01/2014     010001801000007
34,550.00  21/02/2014     010001801000007
4,000.00   21/12/2013     010001801000008
 8,000.00  21/12/2013     010001801000008
12,000.00  21/01/2014     010001801000008
16,000.00  21/02/2014     010001801000008
50,000.00  22/11/2012     010001801000009
100,000.00 22/12/2012     010001801000009
70,000.00  22/01/2014     010001801000010
80,000.00  22/02/2014     010001801000010
1,000.00   23/11/2013     010001801000012
1,500.00   23/12/2013     010001801000012
2,000.00   23/01/2014     010001801000012
2,500.00   23/02/2014     010001801000012
3,000.00   23/03/2014     010001801000012
3,500.00   23/04/2014     010001801000012
500.00     23/01/2014     010001801000013
Posted
Updated 23-Nov-14 22:24pm
v4
Comments
lakshjoshi 24-Nov-14 4:05am    
i am using this query but its not working:--
"SELECT distinct top 10 facno,fbalance,fdate from MAINTRAN where MAINTRAN.fbankcode='" & "010" & "'" & _
"and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
"and MAINTRAN.fbranchcode='" & "01" & "' group by facno,fbalance,fdate "
King Fisher 24-Nov-14 4:05am    
what's your Expected Result?
lakshjoshi 24-Nov-14 4:09am    
just i want only last latest row thats it....i used LAST(),LIMIT,MAX..but its not working i am using it in vb.net
King Fisher 24-Nov-14 4:23am    
when you post your Question add Proper Code Block and Tag
lakshjoshi 24-Nov-14 4:07am    
34,550.00 21/02/2014 010001801000007
16,000.00 21/02/2014 010001801000008
100,000.00 22/12/2012 010001801000009
3,500.00 23/04/2014 010001801000012
500.00 23/01/2014 010001801000013..this is my expected result

You could try something like this.
But the result return, an extra record from the expected, but I am assuming it may have been missed from the list.
SQL
--setup data;
declare @f table (fbalance decimal(10,2), fdate datetime, facno varchar(50));
insert into @f values (10000.00, '21 dec 2013', '010001801000007');
insert into @f values (15000.00, '21 jan 2014', '010001801000007');
insert into @f values (20000.00, '15 jan 2014', '010001801000007');
insert into @f values (29550.00, '21 jan 2014', '010001801000007');
insert into @f values (34550.00, '21 feb 2014', '010001801000007');
insert into @f values (4000.00, '21 dec 2013', '010001801000008');
insert into @f values (8000.00, '21 dec 2013', '010001801000008');
insert into @f values (12000.00, '21 jan 2014', '010001801000008');
insert into @f values (16000.00, '21 feb 2014', '010001801000008');
insert into @f values (50000.00, '22 nov 2012', '010001801000009');
insert into @f values (100000.00, '22 dec 2012', '010001801000009');
insert into @f values (70000.00, '22 jan 2014', '010001801000010');
insert into @f values (80000.00, '22 feb 2014', '010001801000010');
insert into @f values (1000.00, '23 nov 2013', '010001801000012');
insert into @f values (1500.00, '23 dec 2013', '010001801000012');
insert into @f values (2000.00, '23 jan 2014', '010001801000012');
insert into @f values (2500.00, '23 feb 2014', '010001801000012');
insert into @f values (3000.00, '23 mar 2014', '010001801000012');
insert into @f values (3500.00, '23 apr 2014', '010001801000012');
insert into @f values (500.00, '23 jan 2014', '010001801000013');

--get max fdate record for each facno
select 
    f.*
from @f f
inner join (
          select facno, max(fdate) fdateMax
          from @f
          group by facno
    ) fMaxDt
    on 
          f.facno = fMaxDt.facno
          and 
          f.fdate = fMaxDt.fdateMax 
order by 3
;

SQL
--result
fbalance		fdate			facno
34550.00		2014-02-21		010001801000007
16000.00		2014-02-21		010001801000008
100000.00		2012-12-22		010001801000009
80000.00		2014-02-22		010001801000010	--Extra record from the expected
3500.00			2014-04-23		010001801000012
500.00			2014-01-23		010001801000013
--note: facno and fdate need to be unique for the query to return one record for each facno and max facno
 
Share this answer
 
use this :
SQL
select  MAX(convert(datetime,fdate,103)),facno,max(fbalance ) from tbl_test1 group by facno


and one more thing is you must Remove Comma (,) at your fbalance Column you can't Cast into float.
 
Share this answer
 
Comments
Shweta N Mishra 24-Nov-14 4:47am    
this will give wrong output.

Max(balance) may not be of the balance for the Max(convert(datetime,fdate,103)).

Date would be picked from another row and balance from other
King Fisher 24-Nov-14 4:56am    
I'm looking for your Answer buddy :)
jaket-cp 24-Nov-14 4:50am    
I like your one :)
King Fisher 24-Nov-14 4:56am    
Thank you :)
King Fisher 24-Nov-14 4:54am    
both max(fbalance) and max(date) are in Same Row ,you can check with solution3 temp table
Use distinct and order by in your query.
 
Share this answer
 
Comments
lakshjoshi 24-Nov-14 4:04am    
i am using this query but its not working:--
"SELECT distinct top 10 facno,fbalance,fdate from MAINTRAN where MAINTRAN.fbankcode='" & "010" & "'" & _
"and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
"and MAINTRAN.fbranchcode='" & "01" & "' group by facno,fbalance,fdate "
SQL
SELECT TOP 5 fbalance, fdate, facno
FROM
(
	SELECT ROW_NUMBER() OVER( PARTITION BY facno ORDER BY fdate desc) as rowno,
	fbalance,fdate, facno
	FROM YourTable
) AS Top5Recent 
WHERE Rowno=1


Have Fun!
 
Share this answer
 
v3
Comments
lakshjoshi 24-Nov-14 7:09am    
no man still its showing showing duplication when i choose specfic date
Herman<T>.Instance 24-Nov-14 8:13am    
I do not have your database, so which lines do you see double? or for which date given your example in the question?
Herman<T>.Instance 24-Nov-14 8:21am    
whatever I am retesting per date I do not see an doubles appearing. Could you restest by changing 'PARTITION BY facno, fdate' to 'PARTITION BY fdate, facno'
lakshjoshi 24-Nov-14 8:29am    
fbalance fdate facno
10,000.00 21/12/2013 010001801000007
20,000.00 15/01/2014 010001801000007
29,550.00 21/01/2014 010001801000007
34,550.00 21/02/2014 010001801000007//i only need this value not above ones
4,000.00 21/12/2013 010001801000008

and i am using query like this :-
"SELECT TOP 5 fbalance, fdate, facno FROM(SELECT ROW_NUMBER() OVER( PARTITION BY facno, fdate ORDER BY fdate desc) as rowno, fbalance, fdate, facno FROM maintran) AS Top5Recent WHERE Rowno = 1 and fdate<='" & Format(prdate, "yyyy/MM/dd") & "'"
lakshjoshi 24-Nov-14 8:41am    
i want to make you clear
in table
fbalance fdate facno
5000 20/12/2013 010001801000007
10000 21/12/2013 010001801000007//i want this row should be selected and previous row ommited..thats it...and i changed partion by fdate,facno still no to much change...

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


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