How to select last row record

Question

2.71/5 (4 votes)

See more:

Hi everyone out there.Please I want to select the last row record and below is the code I have but am not getting the result I want any help will be grateful.

I appreciate all your effort in trying to help me solve this problem. I have try all the solutions you have provided and am still not getting the result I want. So let me use sample data below to explain things very well.

Example
LedgerNumber LedgerName Amount
4 Gold 300
7 Rice 280
7 Rice 500
4 Gold 80
4 Gold 120
7 Rice 550

Here the sample records above has six rows with three columns. Let say I want to select the last Amount with a LedgerName called Gold. The last amount in this sample data for ledgerName Gold is 120 but the last amount in the table is 550 which has a ledgername called Rice and that is not the target. I hope this explanation will give readers better understanding.

SQL

Select Top (1) Amount From Transactions
Where LedgerName = 'ABC'
Order By LedgerName DESC

Thanks.

Posted 8-Jul-14 1:40am

NanaKwame

Updated 8-Jul-14 13:50pm

v2

Add a Solution

Comments

[no name] 8-Jul-14 7:45am

What is the input? What is the output? What are you getting? How is it not what you want?

Sarvesh Kumar Gupta 8-Jul-14 8:08am

Why you r using Order By, when you r fetching records only for ABC.

[no name] 8-Jul-14 21:02pm

I don't think you can unless you have a column with a unique value to run your query against.

7 solutions

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)

vJay Yadav · Answer 1 · 2014-07-08T02:12:00

Solution 5

try this.. it works

SQL

Select Top (1) Amount From Transactions
Where LedgerName = 'ABC'
Order By Amount DESC

Posted 8-Jul-14 2:12am

vJay Yadav

Abhinav S · Answer 2 · 2014-07-08T01:54:00

Solution 2

Try

Select Top (1) Amount From Transactions<br />
Where LedgerName = 'ABC'<br />
Order By Amount DESC

Replace LedgerName with Amount. Since you choose ABC as the LedgerName, it is the same anyway.

Posted 8-Jul-14 1:54am

Abhinav S

sankarsan parida · Answer 3 · 2014-07-08T15:48:00

if you are really confuse with this solution. THen leave it just do it LINQ and it's simple
no need to order by any thing Just use Last or LastOrDefault method it will return you last record of the table
Get more info
http://www.dotnetperls.com/lastordefault[^]
play-with-last-and-lastordefault-in-linq-to-entities.aspx[^]

Gauri Chodanker · Answer 4 · 2014-07-09T04:47:00

Logically your table should also have a primary key.
If you have it then the below query must work for you

SQL

select t1.primarykey, t1.LedgerNumber, t1.LedgerName , t1.Amount
from table1 t1
inner join
    (
    select LedgerNumber , max(primarykey) primarykey
    from table1
    group by LedgerNumber
    ) t2 on t2.primarykey = t1.primarykey

Dhrisya P Nair · Answer 5 · 2014-07-08T01:53:00

Solution 1

using this code

SQL

select * from Transactions
 where LedgerName in (select top 1 LedgerName from Transactions
Where LedgerName ='ABC'
)
Order By LedgerName DESC

Posted 8-Jul-14 1:53am

Dhrisya P Nair

Updated 9-Jul-14 7:33am

v2

Comments

Sarvesh Kumar Gupta 8-Jul-14 8:08am

Why you r using Order By, when you r fetching records only for ABC.

Hrushikesh_phapale · Answer 6 · 2014-07-08T02:02:00

Solution 4

SQL

select top 1* from(
Select Row_number()over(order by Amount desc)as rowno,*from Transactions Where LedgerName = 'ABC'
)temp

Posted 8-Jul-14 2:02am

Hrushikesh_phapale

Manoj Kumar Choubey · Answer 7 · 2014-07-08T02:02:00

Solution 3

Only need to put DESC and LedgerName before top (1)

SQL

Select Top (1) LedgerName,  Amount From Transactions
Where LedgerName = 'ABC'
Order By LedgerName DESC

Please clear, do you want to search amount only or can ok with tweo column in out put ?

Posted 8-Jul-14 2:02am

Manoj Kumar Choubey

Updated 8-Jul-14 2:03am

v3

Comments

Sarvesh Kumar Gupta 8-Jul-14 8:07am

Why you r using Order By, when you r fetching records only for ABC.

How to select last row record

7 solutions

Solution 5

Solution 2

Solution 7

Solution 9

Solution 1

Solution 4

Solution 3

Add your solution here

Preview 0