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

Answer 1

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.

 

Answer 2

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

Answer 3

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

Answer 4

using  top in sql

Answer 5

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  

Answer 6

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

Answer 7

Many thanks Manish Kumar, the data type of lastdrugpickup is date

Answer 8

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

Answer 9

what is the data type of column lastdrugpickup

Answer 10

SELECT TOP 1 * FROM tbl_RADET ORDER BY lastdrugpickup DESC 

where ptn_pk ='4020' is optional

Answer 11

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