How to Get the Last Record from SQL Table

Question

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 12000 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

Manish Kumar · Answer

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.

Joseph Thomas · Answer

SELECT * FROM `tbl_radet` ORDER BY `tbl_radet` . `LastDrugPickup` DESC LIMIT 1

Satyaprakash Samantaray · Answer

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

Satyaprakash Samantaray · Answer

using top in sql

viral · Answer

hello abraham you use query like this .. select top 1 * from tbl_RADET where ptn_pk = '4020' order by Cast(Convert(nvarchar(30)lastdrugpickup112) as int) desc

Ramesh Barik · Answer

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

Abraham Olatubosun · Answer

Many thanks Manish Kumar  the data type of lastdrugpickup is date

Abraham Olatubosun · Answer

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

Manish Kumar · Answer

what is the data type of column lastdrugpickup

Khaja Moizuddin · Answer

SELECT TOP 1 * FROM tbl_RADET ORDER BY lastdrugpickup DESC where ptn_pk = '4020' is optional

Khaja Moizuddin · Answer

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