Select last group of records in database

Question

0.00/5 (No votes)

See more:

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

SQL

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 18:15pm

sali22

Add a Solution

Comments

ArunRajendra 2-Jul-13 0:51am

What is the criteria for selecting only last 3 records in your screen shoot?

[no name] 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 2-Jul-13 2:53am

You should use cursor.

gvprabu 2-Jul-13 6:20am

give some sample data...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Adarsh chauhan · Answer 1 · 2013-07-01T19:20:00

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.

Posted 1-Jul-13 19:20pm

Adarsh chauhan

Comments

[no name] 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 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

[no name] 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 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