Getting the last record in SQL in WHERE condition
Solution 1
Since the 'last' row for ID 1 is neither the minimum nor the maximum, you are living in a state of mild confusion. Rows in a table have no order. So, you should be providing another column, possibly the date/time when each row is inserted, to provide the sequencing of the data. Another option could be a separate, automatically incremented column which records the sequence in which the rows are inserted. Then the query can be written.
If the extra column is called status_id
, then you could 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 table aliases L1 and L2 could be omitted without confusing the DBMS or experienced SQL programmers.)
As it stands, there is no reliable way of knowing which is the last row, so your query is unanswerable.
Solution 2
Does your table happen to have a primary id or a timestamp? If not then what you want is not really possible.
If yes then:
SELECT TOP 1 status
FROM loanTable
WHERE loan_id = 1
ORDER BY primaryId DESC
-- or
-- ORDER BY yourTimestamp DESC
Solution 3
I assume that with "last status" you mean the record that was inserted most recently? AFAIK there is no way to make such a query unless you add timestamp into your table where you store the date and time when the record was added. RDBMS don't keep any internal order of the records.
Solution 4
But if last = last inserted, that's not possible for current schema, until a PK addition:
select top 1 status, loan_id
from loanTable
where loan_id = 1
order by id desc -- PK
Comments
-
Dinup Kandel over 3 years
i have
loanTable
that contain two fieldloan_id
andstatus
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
ofloan_id
1 i.e isstatus
4. Can please help me in this query. -
Dinup Kandel almost 13 yearsi am not looking for this one. why order by Status desc i didnt understand. actually i was looking the last status of loan_id 1.
-
ain almost 13 yearsNeither of the queries return
4
as per OP's request - both would return6
. -
Dinup Kandel almost 13 yearsthanks probably i didnt know this one i should probably look for another column to look its maximum
-
ρяσѕρєя K over 7 yearsAdd some explanation with answer for how this answer help OP in fixing current issue
-
Umar Aftab over 6 yearsThis query helped me retrieve the latest date in the table.