CODEWITHSUNDEEP
sqlsql-serverdateselect
user6576015
user6576015

Reputation: 45

SQL Server 2008 : SELECT min(date)

I'm trying to do a simple select statement but I'm having a hard time doing it.

I have a table with these columns: 

companyID, CompanyDept, DateAdded

I'm trying to do a select statement like this...

Select CompanyID, CompanyDept 
where min(dateAdded)

So there are multiple dates for DateAdded - I'm trying to select the CompanyDept and COmpanyID by the earliest DateAdded.

EDIT: Select CompanyID, CompanyDEpt where dateAdded=MIN so because there might be three or more dates such as 10/1/2015, 11/12/2015, 1/4/2016 -(rows of data i mean) I'm trying to select this date looking at the earliest possible date (10/1/2016)

Upvotes: 0

Views: 917

Answers (3)

abcool
abcool

Reputation: 95

SELECT CompanyID, CompanyDept 
FROM TableName 
order by dateadd ASC

I assume you want all record , not? you want the 1 record which is old or new for old do ASC and for new do DESC

select top (1)* from CompanyID, CompanyDept 
FROM TableName 
order by dateadd ASC

if not above read below: Q.how do you know that earlier date? is there another column name to compare date with.?

if yes,

where dateadd <= that column
order by dateadd ASC

Upvotes: 1

JamieD77
JamieD77

Reputation: 13949

You can try just getting the first record using TOP 1 and ordering by date.

SELECT TOP 1
    CompanyID, CompanyDept
FROM 
    Table
ORDER BY 
    dateAdded

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

Not totally clear what you want but I think is what you are looking for.

with sortedResults as
(
    Select CompanyID
        , CompanyDept 
        , ROW_NUMBER() over (partition by CompanyID, CompanyDept order by dateAdded desc) as RowNum 
    from YourTable
)

select CompanyID
    , CompanyDept
from sortedResults
where RowNum = 1

Upvotes: 1

Related Questions