How to Get the Last Record from SQL Table

Abraham Olatubosun

9y5.1kSQL

Hi my code masters, i have setup a table called tbl_RADET below is the image of data's imputed by our data entry clarck.

we have about 12,000 record in this table, the paitentID is duplicated due to different date of facility visit, i want to retrive the record with the most current date, from this table i have use lot of sql statement but i am not geting the desired result, i have also used

select * from tbl_RADET where ptn_pk ='4020' order by lastdrugpickup desc

the SQL statement above did not give the right result, from the above we can see that line 17 is the expected result.

please i need your help. thank you

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyaprakash Samantaray9y ago

SELECT * FROM TableName WHERE id=(SELECT max(id) FROM TableName);

Satyaprakash Samantaray9y ago
```
using  top in sql
```
0
viral9y ago
hello abraham,

you use query like this ..

select top 1 * from tbl_RADET where ptn_pk ='4020' order by Cast(Convert(nvarchar(30),lastdrugpickup,112) as int) desc
0
Manish Kumar9y ago
If lastdrugpickup datatype is date then wondering how you have saved date in dd/mm/yyyy format
if you run below query it will not allow to run, because it will only store date in yyyy-mm-dd format

declare @date date
set @date='21/01/2016'
select @date

and for sorting i guess sql server assumes lastdrugpickup as varchar column and not able to sort it.
0
Joseph Thomas9y ago
SELECT * FROM `tbl_radet` ORDER BY `tbl_radet`.`LastDrugPickup` DESC LIMIT 1
+1
Ramesh Barik9y ago
WITH Res
AS (SELECT
ROW_NUMBER() OVER (
PARTITION BY tbl_RADET.PatientID ORDER BY LastDrugDate DESC
) AS Row
,Ptn_pk
,PatientID
,ARTStartDate
,LastDrugDate
FROM tbl_RADET where ptn_pk ='4020')
SELECT
*
FROM Res
WHERE ROW = 1
0
Abraham Olatubosun9y ago
Many thanks Manish Kumar, the data type of lastdrugpickup is date
0
Abraham Olatubosun9y ago
Many Thanks Khaja Moizuddin,
I have tried
SELECT TOP 1 * FROM tbl_RADET ORDER BY lastdrugpickup DESC
this just return the last row in the table, but not the most current LastDrugPickup date.
I also tried
select * from tbl_RADET where ptn_pk ='4020' order by 1 desc which give the same result as Select * from tbl_RADET where ptn_pk = '4020'.
so far all these solution is not giving expected result. i will appreciate any solution
0
Manish Kumar9y ago
what is the data type of column lastdrugpickup
0

Khaja Moizuddin9y ago

SELECT TOP 1 * FROM tbl_RADET ORDER BY lastdrugpickup DESC

where ptn_pk ='4020' is optional

Khaja Moizuddin9y ago
select * from tbl_RADET where ptn_pk ='4020' order by 1 desc
0