0

Let's say this is my table TicketUpdate in SQL Server with some data inside:

_______________________________
| Id | TicketId | Description |
-------------------------------
| 1  |   5      |   desc1     |
| 2  |   6      |   desc2     |
| 3  |   5      |   desc3     |
| 4  |   5      |   desc4     |
| 5  |   6      |   desc5     |

I want to retrieve the last row with TicketId = 5 in using Petapoco.

There are several methods for retrieving single row like FirstOrDefault which looks like:

db.FirstOrDefault<TicketUpdate>("select * from TicketUpdate where TicketId = 5");

But using this statement it returns the first row with value of TicketId = 5 with a description of desc1.

My question is how can I retrieve the LastOrDefault value then? There is no such methods in Petapoco.

Additional info

Temporarily I can retrieve the last row with TicketId = 5 by nesting the query like

select * 
from TicketUpdate 
where Id = (select MAX(Id) from TicketUpdate where TicketId = 5)

But is there any methods or better approach for finding the last row like we retrieve First row by using FirstOrDefault method, without nesting the query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shree Krishna
  • 8,474
  • 6
  • 40
  • 68
  • 6
    Asking for "first" or "last" when you don't specify an *order* is effectively "give me a random row". If you *do* specify an order, you don't need `LastOrDefault` - or, in fact, you do already have `LastOrDefault`. It's spelled `FirstOrDefault` but you use the opposite order than you used previously. – Damien_The_Unbeliever May 27 '16 at 07:09
  • 2
    I have to agree with @Damien_The_Unbeliever. The table itself is not ordered, unless you specify the order manually. Therefore LastOrDefault() would not make sense. Instead you can use OrderByDesc(...).FirstOrDefault(). – smoksnes May 27 '16 at 07:13
  • `select * from TicketUpdate where TicketId = 5 order by ID desc` – Mairaj Ahmad May 27 '16 at 07:13

2 Answers2

3

As mentioned in the comments, you should be able to sort your data first. Try something like this:

db.FirstOrDefault<TicketUpdate>("select TOP 1 * from TicketUpdate where TicketId = 5 orderby [Id] desc");

As long as Id is incremented it should return the last item added for TicketId == 5.

smoksnes
  • 10,509
  • 4
  • 49
  • 74
  • Thank you very much for answering, I already fixed it, But the first credit goes to Damien_The_Unbeliever. any way thanks for the effort. – Shree Krishna May 27 '16 at 07:21
1

Change your query to get the last record where ID is max. Also you need to use Top 1 to get only one record.

select Top 1 * from TicketUpdate where TicketId = 5 order by ID desc
Mairaj Ahmad
  • 14,434
  • 2
  • 26
  • 40
  • thank you, But I fixed it immediately after Damien_The_Unbeliever commented, Really my mind was hanged, I even didn't think about ordering, So it's my mistake.. Anyway thanks – Shree Krishna May 27 '16 at 08:44