EXTRACT date from field

[sal21]

Attached is a xls sheet is the structure of my table.
In this table is a field named AGG (is format as date)

I need to exctract from field AGG the newest date (in this case 15/10/2010) and the first date near that, in this case 30/09/2010... how to with sql query.

[HansV]

To get the latest date:

SELECT Max(AGG) AS LASTAGG FROM MyTable

where MyTable is the name of the table.

To get the next latest date:

SELECT Max(AGG) AS NEXTTOLASTAGG FROM MyTable WHERE AGG<(SELECT Max(AGG) FROM MyTable)

[sal21]

To get the latest date:

SELECT Max(AGG) AS LASTAGG FROM MyTable

where MyTable is the name of the table.

To get the next latest date:

SELECT Max(AGG) AS NEXTTOLASTAGG FROM MyTable WHERE AGG<(SELECT Max(AGG) FROM MyTable)

Very slow the second query....
i have 234.458 dates in column

[HansV]

Here is another option:

1) Create a query with the following SQL:

SELECT DISTINCT TOP 2 AGG
FROM MyTable
ORDER BY AGG DESC

Save it as qryTop2.

2) Create a second query with the following SQL:

SELECT Min(AGG) AS NEXTTOLASTAGG
FROM qryTop2

It'll help if your table is indexed on AGG.

[sal21]

Here is another option:

1) Create a query with the following SQL:

SELECT DISTINCT TOP 2 AGG
FROM MyTable
ORDER BY AGG DESC

Save it as qryTop2.

2) Create a second query with the following SQL:

SELECT Min(AGG) AS NEXTTOLASTAGG
FROM qryTop2

It'll help if your table is indexed on AGG.

Pasta, pizza and

[HansV]