Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
Dear Database Experts,
 
I have a table in MSSQL 2012 and I want to read last group of records(It can be 1 record or more) base on the one filed.
 
as far as I know if I want to select last record I can use the below sql ,, but for reading a group of records (number of records depends on the value of one field), I don't have idea
 
SELECT TOP 1 DEBIT,TRANS_DESC FROM LGR WHERE REFERENCE="8888" ORDER BY TRANS_NO DESC
 

sample table
 
Your help is greatly appreciated
Thanks in advance
Posted 1-Jul-13 19:15pm
sali22390
Comments
ArunRajendra at 2-Jul-13 0:51am
   
What is the criteria for selecting only last 3 records in your screen shoot?
sali22 at 2-Jul-13 3:07am
   
the criteria is based on the TRANS_DESC ,, if you click in the sample table ,, you will see in this example in need to retrieve last 3 records,,
jaideepsinh at 2-Jul-13 2:53am
   
You should use cursor.
gvprabu at 2-Jul-13 6:20am
   
give some sample data...

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
you have circled trans_Desc 182. if that means you want to select on this criteria...
you can do this as
 
SELECT *  FROM LGR WHERE TRANS_DESC 
in ( select Max(TRANS_DESC) from LGR )
 
it will display a group of records..
 
and if your purpose is something diff. then please explain little more about the purpose.
  Permalink  
Comments
sali22 at 2-Jul-13 2:12am
   
Thanks for your reply, but No!

I meant ,, I have one table and I simply want to select last 3 records (in this example)

As you can see in this Sample Table ,, in TRANS_DESC last 3 codes are same (1820) ,, so I need to retrieve SUM of the amount of DEBITs for last three records ,

My expected result is : 1406.88
Adarsh chauhan at 2-Jul-13 2:32am
   
Do you mean this...
 
select Referance,trans_date,trans_desc,sum(Debit) Total_Debit from
(
SELECT TOP 3 * FROM LGR ORDER BY TRANS_NO DESC
)tbl1
group by Referance,trans_date,trans_desc
sali22 at 2-Jul-13 2:49am
   
Thanks Adarsh ,, for this example it works ,,
but 3 is not constant ,, it can be variable (based on TRANS_DESC)
Adarsh chauhan at 2-Jul-13 3:07am
   
m happy that it worked for you.. :)
but m having a doubt.. can you please tell me that how will you get that whthr u have to select top 3 or 4 on n no. of rows???
 
would not it be btr if you do it like this...( in case if you are bothered about trans_desc only)
 
select Referance,trans_date,trans_desc,sum(Debit) Total_Debit from LGR
where trans_desc=182
group by Referance,trans_date,trans_desc

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

  Print Answers RSS
0 OriginalGriff 375
1 Sergey Alexandrovich Kryukov 329
2 CPallini 260
3 DamithSL 214
4 Maciej Los 197
0 OriginalGriff 5,455
1 DamithSL 4,457
2 Maciej Los 3,885
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,115


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 2 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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