CODEWITHSUNDEEP
sqlsql-servert-sqldategreatest-n-per-group
Asif Hameed
Asif Hameed

Reputation: 1423

Getting oldest record from SQL Server using T-SQL

I have a T-SQL query where I need to find the oldest date record. I have written it like this:

SELECT
    T0.ItemCode, T0.OpenQty AS 'Qty', MIN(T1.DocDate) AS 'DocDate', 
    T1.DocNum, T1.CardCode, T1.SlpCode AS 'Sales Person', T0.WhsCode
FROM
    RDR1 T0
INNER JOIN
    ORDR T1 ON T0.DocEntry = T1.DocEntry
WHERE 
    T0.linestatus = 'O'
GROUP BY 
    T0.ItemCode, T0.OpenQty , T1.DocNum, T1.CardCode, T1.SlpCode , T0.WhsCode

but it is not returning the oldest date record.

Data is shown in this screenshot:

enter image description here

Upvotes: 0

Views: 107

Answers (2)

Ashutosh Singh
Ashutosh Singh

Reputation: 91

Why are you writing Self join ? If you need to get 10 oldest record from table then just sort the table according date in ascending order and list top 10 rows.

For Example - 

Select  top 10 T0.ItemCode, T0.OpenQty as 'Qty', T1.DocDate as 'DocDate', T1.DocNum, T1.CardCode, T1.SlpCode as 'Sales Person', T0.WhsCode
From RDR1 T0
Where T0.linestatus = 'O'
order by T1.DocDate

Upvotes: 2

GMB
GMB

Reputation: 222582

You can use ROW_NUMBER() to rank the records by ascending T1.DocDate in a inner query, and then select the oldest in the outer query:

Select *
From (
    Select  
        T0.ItemCode, 
        T0.OpenQty as 'Qty', 
        T1.DocDate,
        T1.DocNum, 
        T1.CardCode, 
        T1.SlpCode as 'Sales Person', 
        T0.WhsCode,
        Row_Number() Over(Order By T1.DocDate) rn
    From RDR1 T0
    Inner Join ORDR T1 on T0.DocEntry = T1.DocEntry
    Where T0.linestatus = 'O'
) x
Where rn = 1

This will give you the oldest record in the table. If you need to get the oldest record per group, then you can add a PARTITION BY clause to the ROW_NUMBER() function.

Note: this also assumes that T1.DocDate is of a date-like datatype, which seems to be (and should be) the case by looking at your sample data.

Upvotes: 3

Related Questions