CODEWITHSUNDEEP
sqlsql-serverselect
Jose Mario Jiménez Alfaro
Jose Mario Jiménez Alfaro

Reputation: 91

Select Oldest Dates

I want to select the Oldest Dates values on a Date Column.

Example: I want to get 30% of the Oldest Dates. If I have 10 Registers, I want to get the bottom/oldest 3.

Example 2:

If: John has been working since 2005 Gina has been working since 2003 Mark has been working since 2000 Lou has been working since 2015

I want to get the Mark and Gina, because they've been working more time in the company.

Upvotes: 0

Views: 440

Answers (1)

Squirrel
Squirrel

Reputation: 24813

Basically you still use SELECT TOP statement, just change the ORDER BY with ASC or DESC to return the result in ascending or descending order

Here are some sample query to illustrate

-- Create a Sample Table
declare @sample table
(
    date_col    date
)

-- Insert some sample dates
insert into @sample select getdate()
insert into @sample select getdate() - 1
insert into @sample select getdate() - 2
insert into @sample select getdate() - 3
insert into @sample select getdate() - 4
insert into @sample select getdate() - 5

-- Get TOP 3 rows order by date in ascending (oldest to latest)
select  top 3 *
from    @sample
order by date_col

-- Get TOP 3 rows order by date in descending (latest to oldest)
select  top 3 *
from    @sample
order by date_col desc

-- Get TOP 30 percent, total 6 rows so 30% is 2 rows in ascending order
select  top (30) percent *
from    @sample
order by date_col

-- in descending order
select  top (30) percent *
from    @sample
order by date_col desc

Upvotes: 1

Related Questions