Getting last record in SQL in WHERE clause

Question

i have loanTable which contain two fields loan_id and status

 loan_id status ============== 1 0 2 9 1 6 5 3 4 5 1 4 <-- How do I select this?? 4 6 

In this situation, I need to show the last status of loan_id 1 ie is status 4. Can I help with this request.

Answer 1

Since the "last" line for ID 1 is neither a minimum nor a maximum, you live in a state of easy confusion. The rows in the table have no order. Thus, you should provide a different column, possibly the date / time, when each row is inserted to ensure data consistency. Another option would be a separate, automatically incrementing column that records the sequence in which rows are inserted. Then the request can be recorded.

If the extra column is called status_id , you can write:

 SELECT L1.* FROM LoanTable AS L1 WHERE L1.Status_ID = (SELECT MAX(Status_ID) FROM LoanTable AS L2 WHERE L2.Loan_ID = 1); 

(The aliases of table L1 and L2 can be omitted without confusion with the DBMS or experienced SQL programmers.)

Be that as it may, there is no reliable way to know which one is the last, so your request is not possible.

Answer 2

Does your table have a primary identifier or timestamp? If not, then what you want is actually impossible.

If yes, then:

  SELECT TOP 1 status FROM loanTable WHERE loan_id = 1 ORDER BY primaryId DESC -- or -- ORDER BY yourTimestamp DESC 

Answer 3

I assume with "last status" you mean a record that was inserted recently? AFAIK there is no way to make such a request unless you add a timestamp to your table, where you will store the date and time the record was added. RDBMSs do not preserve the internal order of records.

Answer 4

But if last = last insert, this is not possible for the current scheme, before adding PK:

 select top 1 status, loan_id from loanTable where loan_id = 1 order by id desc -- PK 

Answer 5

Use a data reader. When it exits the while loop, it will be on the last line. As other posters claimed, if you did not put a sort on demand, the line order can change. Even if the table has a clustered index, it may not return rows in that order (without sorting by the clustered index).

  SqlDataReader rdr = SQLcmd.ExecuteReader(); while (rdr.Read()) { } string lastVal = rdr[0].ToString() rdr.Close(); 

You can also use ROW_NUMBER (), but this requires sorting, and you cannot use ROW_NUMBER () directly in the Where folder. But you can trick it by creating a view. Rdr solution above is faster.

Answer 6

In the oracle database it is very simple.

select * from (select * from loanTable order by rownum desc), where rownum = 1

Answer 7

Hi, if this has not been decided yet. To get the last record for any field from the table, the easiest way would be to add an ID for each record, for example, pID. Also say that in your table you want to write the last entry for each "name", run a simple query

 SELECT Name, MAX(pID) as LastID INTO [TableName] FROM [YourTableName] GROUP BY [Name]/[Any other field you would like your last records to appear by] 

You should now have a table containing the names in one column and the last identifier available for that name. Now you can use the join to get other data from your main table, say this is some price or date, and do the following:

 SELECT a.*,b.Price/b.date/b.[Whatever other field you want] FROM [TableName] a LEFT JOIN [YourTableName] ON a.Name = b.Name and a.LastID = b.pID 

Then you should give you the latest entries for each name, for the first entry, follow the same prompts as above, just replace Max with Min above.

It should be easy to follow and should be faster.

Answer 8

If you do not have identification columns that you can use to get the insertion order. You can always do it like this. But it is hacked, and not very beautiful.

 select t.row1, t.row2, ROW_NUMBER() OVER (ORDER BY t.[count]) AS rownum from ( select tab.row1, tab.row2, 1 as [count] from table tab) t 

So basically you get a “natural order” if you can name it, and add a column with all the same data. This can be used to sort by "natural order", which gives you the ability to put the column of the row number in the next query.

Personally, if the system you are using does not have a time stamp / identifier, and current users use the "natural order", I would quickly add a column and use this query to create a kind of time stamp / incremental key. Instead of risking that some kind of automation mechanism changes the "natural order", break the necessary data.

Answer 9

I think this code can help you:

 WITH cte_Loans AS ( SELECT LoanID ,[Status] ,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RN FROM LoanTable ) SELECT LoanID ,[Status] FROM LoanTable L1 WHERE RN = ( SELECT max(RN) FROM LoanTable L2 WHERE L2.LoanID = L1.LoanID)