N K N K - 6 months ago 11
SQL Question

Get the lastest record from the query

I have a query which gives me 5 records in the

sql
, but I want to show one record which is latest one.

Below is my query

Select a.mkey, c.type_desc DOC_TYPE, a.doc_no INWARD_NO,
convert(varchar, a.doc_date,103)date,
a.to_user, a.No_of_pages, Ref_No, d.type_desc DEPT_RECEIVED, b.first_name + ' ' +
b.last_name EMP_RECEIVED, b.first_name + ' ' + b.last_name NAME,
b.email
from inward_doc_tracking_hdr a , user_mst b ,type_mst_a c,
type_mst_a d
where a.to_user = b.mkey and a.doc_type = c.master_mkey
and a.dept_received = d.Master_mkey and a.to_user = '1260'


Below is the result

[![Query][1]][1]

I am using
sql-server-2005


I tried with
TOP1
but it is not giving me the latest record

Answer

You need to put an ORDER BY in your query. Without the ORDER BY clause, there is no guarantee that the TOP command will return the expected result:

SELECT TOP 1
    <column_list>
FROM ....
ORDER BY a.doc_date DESC

Additionally, you should avoid using the old-style JOIN syntax.