How can I achieve the following: Suppose I have a table with a colum
holding a date. Furthermore, I have a specific date t. Now I want to
select all colums whose dates are before t and that are closest to t.

Example: The table holds rows with entries t1, t2, t3 in the date
column with t1 < t2 < t3. t is between t2 and t3. Therefore, I want
to select t2.

I can imagine two apporaches:
1) First, select all rows that have a date before t with <=. Then,
select all rows of the former result that have the latest date with
max.
2) Include another column that holds dates. The second date of a row
holds the "next" date. The two dates then indicate the "time range" of
the row. In the example above, one could then select the correct rows
with BETWEEN in one step.

Approach 1) should work fine. But somehow I feel like it wastes
performance. The number of rows can become very large.

Approach 2) works in my case because new data will always have dates
"after" old data. Therefore, only the second date entry of the
"latest" old rows have to be updated.

Is there a better way to achive my goal?

Thanks, Ulrich

Ulrich Scholz <d7 (AT) thispla (DOT) net> wrote:

SELECT ...
WHERE date_column < t
ORDER BY date_column DESC
LIMIT 1


XL

On 06-09-2011 17:08, Axel Schwenke wrote:
This will fail when there are more records which match the date in
date_column that is found.


SELECT ....
WHERE date_column = ( SELECT date_column
FROM ..
WHERE date_column < t
ORDER BY data column DESC LIMIT 1)

--
Luuk

Luuk <Luuk (AT) invalid (DOT) lan> wrote:
Maybe you have more information than I have, but Ulrich did not say he
wants *all rows* that are below and next to t. He speaks of columns
and in the topic he asks to find "a date ..." which I read as "some row
for that date"


XL

On 6 Sep., 22:31, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:
Sorry. Of course what I meant to say was "I want to
select all *rows* whose dates are before t and that are closest to
t."

Thanks for your help.

Ulrich

Ulrich Scholz wrote:
It may be your english is at fault but the adjective 'closest' implies
uniqueness.
I.e. give the question 'which is closest to New York'

there is only one answer from

Washington, Madrid, Tokyo, London...

In article <4e667a49$0$2454$e4fe514c (AT) news2 (DOT) news.xs4all.nl>,
Luuk <Luuk (AT) invalid (DOT) lan> wrote:
Also, make sure you have an index on date_column.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

On 9/7/2011 4:22 AM, The Natural Philosopher wrote:
Learn to read, idiot. "Closest date" would generally indicate a unique
DATE. It doesn't necessarily indicate a unique ROW.

If you had any brains at all you'd be dangerous!



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Wed, 07 Sep 2011 09:22:56 +0100, The Natural Philosopher wrote:
The point of contention is that there/could be MANY "Washington" rows.
You don't get to change the arguement, AFTER clarification no less, to
make yourself correct.

--
Progress (n.): The process through which Usenet has evolved from
smart people in front of dumb terminals to dumb people in front
of smart terminals.
-- obs (AT) burnout (DOT) demon.co.uk

On 9/6/2011 10:41 AM, Ulrich Scholz wrote:
Hi, Ulrich,

I don't know why others had a problem with your question - it was very
clear to me. What I understood was:

Let's say you have (English version - don't get your panties in a wad, TNP!)

Sept 1 (5 rows)
Sept 3 (3 rows)
Sept 5 (4 rows)

And your select criteria is Sept 4, then you want the 3 rows from
September 3.

The following should do it for you:

use temp;
drop table if exists datetest;
create table datetest (
id int not null auto_increment primary key,
eventdate date not null,
event varchar(255) not null);

insert into datetest (eventdate, event)
VALUES ('2011-09-01', 'Department meeting'),
('2011-09-01', 'Status report due'),
('2011-09-01', 'Finish 4Q financial projections'),
('2011-09-01', 'Attend son''s soccor game'),
('2011-09-01', 'Take trash cans to street'),
('2011-09-03', 'Clean Garage'),
('2011-09-03', 'Wash car'),
('2011-09-03', 'Mow lawn'),
('2011-09-05', 'Sleep in'),
('2011-09-05', 'Put up flag'),
('2011-09-05', 'Take family on picnic'),
('2011-09-05', 'Get stuff ready for work');

SELECT *
FROM datetest
WHERE eventdate = (
SELECT MAX(eventdate)
FROM datetest
WHERE eventdate < '2011-09-04');

+----+------------+--------------+
3 rows in set (0.15 sec)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================