How to select a date that is "most recent" before another date

#31

On 9/12/2011 5:35 PM, Jerry Stuckle wrote:

Quote:

On 9/12/2011 4:37 PM, Haym Solomon wrote:
On 9/8/2011 7:30 PM, Jerry Stuckle wrote:
On 9/8/2011 9:47 AM, Haym Solomon wrote:
On 9/7/2011 11:40 PM, Jerry Stuckle wrote:
On 9/7/2011 8:50 PM, Haym Solomon wrote:
On 9/6/2011 3:53 PM, Luuk wrote:
On 06-09-2011 17:08, Axel Schwenke wrote:
Ulrich Scholz<d7 (AT) thispla (DOT) net> wrote:

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.

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

XL

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)

Why will it fail in that case. The query select ALL the records
with a
date before the given date, sorts them in descending order, and then
provides only the first in that list. I can't see what is wrong with
the
original. If more than one have that same date, the result will be
unpredictable as to which of the them is returned, but since that
wasn't
part of the original question, so what?

Did you actually try it?

I thought not.

LIMIT is not valid in a subselect in MySQL.

Mr. Stuckle, you obviously misread/misunderstood my reply. I was asking
why the poster who put up the one with the subselect said that the
FIRST
reply

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

wouldn't work. It will.

So, rather than getting immediately nasty, perhaps you should first try
to improve your reading skills. Your apology for your nastiness will be
accepted.

No, it will NOT work. This will return one row - not ALL rows with the
specified date.

So maybe YOU need improve YOUR reading skills.

The OP asked for ONE row. Later, AFTER these posts appeared, he said he
made a mistake and really wanted all rows, not just one. So, it WILL
work in response to the OP's ORIGINAL request, and YOU need to improve
YOUR reading skills, Mr. Nasty.

No, you need to understand what he MEANS - which is NOT NECESSARILY WHAT
HE SAYS. Taking the entire context, it was obvious he wanted all the rows.

Now it is Jerry the Mindreader? He said ONE. To answer for ALL rather
than one is, in a word, WRONG.

Quote:

And your solution will return ONE ROW, not ALL ROWS.

Of course. That was what he requested. Thank you for finally realizing
that the answer given was CORRECT.

Quote:

And take your attitude and stuff it where the sun doesn't shine - that
way it will be right next to your head.

Sorry Jerry, but in trying to get close to your brains, the smell is too
much, and besides, I am not inclined to stick my head there, even though
the sun don't shine there.

But, since you brought up the topic, WHAT AN ASSHOLE! I could think of
many other words -- obnoxious, egomaniac, pompous, etc. but asshole fits
the best of all.

BTW, I was correct in my response to the OP's initial request and you
were wrong. What he asked for later is irrelevant to this particular
discussion.

#32

Haym Solomon wrote:

Quote:

Of course. That was what he requested. Thank you for finally realizing
that the answer given was CORRECT.

There is. by Stuckle *definition*, only one *correct* answer, and it's

his, right or wrong.

I thought everybody knew that already....

Quote:

And take your attitude and stuff it where the sun doesn't shine - that
way it will be right next to your head.

Sorry Jerry, but in trying to get close to your brains, the smell is too
much, and besides, I am not inclined to stick my head there, even though
the sun don't shine there.

But, since you brought up the topic, WHAT AN ASSHOLE! I could think of
many other words -- obnoxious, egomaniac, pompous, etc. but asshole fits
the best of all.

Google 'right man'

It's a known psychological syndrome.

#33

On 9/13/2011 11:41 AM, Haym Solomon wrote:

Quote:

On 9/12/2011 5:35 PM, Jerry Stuckle wrote:
On 9/12/2011 4:37 PM, Haym Solomon wrote:
On 9/8/2011 7:30 PM, Jerry Stuckle wrote:
On 9/8/2011 9:47 AM, Haym Solomon wrote:
On 9/7/2011 11:40 PM, Jerry Stuckle wrote:
On 9/7/2011 8:50 PM, Haym Solomon wrote:
On 9/6/2011 3:53 PM, Luuk wrote:
On 06-09-2011 17:08, Axel Schwenke wrote:
Ulrich Scholz<d7 (AT) thispla (DOT) net> wrote:

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.

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

XL

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)

Why will it fail in that case. The query select ALL the records
with a
date before the given date, sorts them in descending order, and then
provides only the first in that list. I can't see what is wrong with
the
original. If more than one have that same date, the result will be
unpredictable as to which of the them is returned, but since that
wasn't
part of the original question, so what?

Did you actually try it?

I thought not.

LIMIT is not valid in a subselect in MySQL.

Mr. Stuckle, you obviously misread/misunderstood my reply. I was
asking
why the poster who put up the one with the subselect said that the
FIRST
reply

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

wouldn't work. It will.

So, rather than getting immediately nasty, perhaps you should first
try
to improve your reading skills. Your apology for your nastiness
will be
accepted.

No, it will NOT work. This will return one row - not ALL rows with the
specified date.

So maybe YOU need improve YOUR reading skills.

The OP asked for ONE row. Later, AFTER these posts appeared, he said he
made a mistake and really wanted all rows, not just one. So, it WILL
work in response to the OP's ORIGINAL request, and YOU need to improve
YOUR reading skills, Mr. Nasty.

No, you need to understand what he MEANS - which is NOT NECESSARILY WHAT
HE SAYS. Taking the entire context, it was obvious he wanted all the
rows.

Now it is Jerry the Mindreader? He said ONE. To answer for ALL rather
than one is, in a word, WRONG.

No, it's READING IN CONTEXT. The rest of the context made it clear he
was looking for multiple rows for one date.

Sometimes people don't use the right words when explaining what they
want. Examining the context shows the true meaning.

Quote:

And your solution will return ONE ROW, not ALL ROWS.

Of course. That was what he requested. Thank you for finally realizing
that the answer given was CORRECT.

Only to someone who doesn't try to understand what the op MEANS.

Quote:

And take your attitude and stuff it where the sun doesn't shine - that
way it will be right next to your head.

Sorry Jerry, but in trying to get close to your brains, the smell is too
much, and besides, I am not inclined to stick my head there, even though
the sun don't shine there.

But, since you brought up the topic, WHAT AN ASSHOLE! I could think of
many other words -- obnoxious, egomaniac, pompous, etc. but asshole fits
the best of all.

BTW, I was correct in my response to the OP's initial request and you
were wrong. What he asked for later is irrelevant to this particular
discussion.

What he asked for later is exactly the same as what he asked for
earlier, when you examine the CONTEXT.

One of the tricks of becoming a good programmer (and a good project
manager) is to understand what the user MEANS. The vast majority of the
time it's not what he SAYS.

But you just can't admit you're too stoopid to even go back and try to
understand the context. It was very clear to me.

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

#34

On 9/13/2011 12:51 PM, Jerry Stuckle wrote:

Quote:

On 9/13/2011 11:41 AM, Haym Solomon wrote:
On 9/12/2011 5:35 PM, Jerry Stuckle wrote:
On 9/12/2011 4:37 PM, Haym Solomon wrote:
On 9/8/2011 7:30 PM, Jerry Stuckle wrote:
On 9/8/2011 9:47 AM, Haym Solomon wrote:
On 9/7/2011 11:40 PM, Jerry Stuckle wrote:
On 9/7/2011 8:50 PM, Haym Solomon wrote:
On 9/6/2011 3:53 PM, Luuk wrote:
On 06-09-2011 17:08, Axel Schwenke wrote:
Ulrich Scholz<d7 (AT) thispla (DOT) net> wrote:

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.

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

XL

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)

Why will it fail in that case. The query select ALL the records
with a
date before the given date, sorts them in descending order, and
then
provides only the first in that list. I can't see what is wrong
with
the
original. If more than one have that same date, the result will be
unpredictable as to which of the them is returned, but since that
wasn't
part of the original question, so what?

Did you actually try it?

I thought not.

LIMIT is not valid in a subselect in MySQL.

Mr. Stuckle, you obviously misread/misunderstood my reply. I was
asking
why the poster who put up the one with the subselect said that the
FIRST
reply

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

wouldn't work. It will.

So, rather than getting immediately nasty, perhaps you should first
try
to improve your reading skills. Your apology for your nastiness
will be
accepted.

No, it will NOT work. This will return one row - not ALL rows with the
specified date.

So maybe YOU need improve YOUR reading skills.

The OP asked for ONE row. Later, AFTER these posts appeared, he said he
made a mistake and really wanted all rows, not just one. So, it WILL
work in response to the OP's ORIGINAL request, and YOU need to improve
YOUR reading skills, Mr. Nasty.

No, you need to understand what he MEANS - which is NOT NECESSARILY WHAT
HE SAYS. Taking the entire context, it was obvious he wanted all the
rows.

Now it is Jerry the Mindreader? He said ONE. To answer for ALL rather
than one is, in a word, WRONG.

No, it's READING IN CONTEXT. The rest of the context made it clear he
was looking for multiple rows for one date.

Sometimes people don't use the right words when explaining what they
want. Examining the context shows the true meaning.

And your solution will return ONE ROW, not ALL ROWS.

Of course. That was what he requested. Thank you for finally realizing
that the answer given was CORRECT.

Only to someone who doesn't try to understand what the op MEANS.

And take your attitude and stuff it where the sun doesn't shine - that
way it will be right next to your head.

Sorry Jerry, but in trying to get close to your brains, the smell is too
much, and besides, I am not inclined to stick my head there, even though
the sun don't shine there.

But, since you brought up the topic, WHAT AN ASSHOLE! I could think of
many other words -- obnoxious, egomaniac, pompous, etc. but asshole fits
the best of all.

BTW, I was correct in my response to the OP's initial request and you
were wrong. What he asked for later is irrelevant to this particular
discussion.

What he asked for later is exactly the same as what he asked for
earlier, when you examine the CONTEXT.

One of the tricks of becoming a good programmer (and a good project
manager) is to understand what the user MEANS. The vast majority of the
time it's not what he SAYS.

But you just can't admit you're too stoopid to even go back and try to
understand the context. It was very clear to me.

Jerry, the word you want was "stupid", not "stoopid". It is not the id
of a set of stairs to a building. Learn how to spell.

There is an old saying: "Say what you mean and mean what you say". If
you go into a restaurant and want a steak medium rare, to you say
"medium" and expect the chef to read your mind as to what you _really_
wanted?

Anyway, I leave this thread with one word to you Mr. Unnecessarily
Nasty, who can't hold a civil conversation -- WHATEVER!

#35

On 9/13/2011 4:56 PM, Haym Solomon wrote:

Quote:

On 9/13/2011 12:51 PM, Jerry Stuckle wrote:
On 9/13/2011 11:41 AM, Haym Solomon wrote:
On 9/12/2011 5:35 PM, Jerry Stuckle wrote:
On 9/12/2011 4:37 PM, Haym Solomon wrote:
On 9/8/2011 7:30 PM, Jerry Stuckle wrote:
On 9/8/2011 9:47 AM, Haym Solomon wrote:
On 9/7/2011 11:40 PM, Jerry Stuckle wrote:
On 9/7/2011 8:50 PM, Haym Solomon wrote:
On 9/6/2011 3:53 PM, Luuk wrote:
On 06-09-2011 17:08, Axel Schwenke wrote:
Ulrich Scholz<d7 (AT) thispla (DOT) net> wrote:

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.

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

XL

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)

Why will it fail in that case. The query select ALL the records
with a
date before the given date, sorts them in descending order, and
then
provides only the first in that list. I can't see what is wrong
with
the
original. If more than one have that same date, the result will be
unpredictable as to which of the them is returned, but since that
wasn't
part of the original question, so what?

Did you actually try it?

I thought not.

LIMIT is not valid in a subselect in MySQL.

Mr. Stuckle, you obviously misread/misunderstood my reply. I was
asking
why the poster who put up the one with the subselect said that the
FIRST
reply

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

wouldn't work. It will.

So, rather than getting immediately nasty, perhaps you should first
try
to improve your reading skills. Your apology for your nastiness
will be
accepted.

No, it will NOT work. This will return one row - not ALL rows with
the
specified date.

So maybe YOU need improve YOUR reading skills.

The OP asked for ONE row. Later, AFTER these posts appeared, he
said he
made a mistake and really wanted all rows, not just one. So, it WILL
work in response to the OP's ORIGINAL request, and YOU need to improve
YOUR reading skills, Mr. Nasty.

No, you need to understand what he MEANS - which is NOT NECESSARILY
WHAT
HE SAYS. Taking the entire context, it was obvious he wanted all the
rows.

Now it is Jerry the Mindreader? He said ONE. To answer for ALL rather
than one is, in a word, WRONG.

No, it's READING IN CONTEXT. The rest of the context made it clear he
was looking for multiple rows for one date.

Sometimes people don't use the right words when explaining what they
want. Examining the context shows the true meaning.

And your solution will return ONE ROW, not ALL ROWS.

Of course. That was what he requested. Thank you for finally realizing
that the answer given was CORRECT.

Only to someone who doesn't try to understand what the op MEANS.

And take your attitude and stuff it where the sun doesn't shine - that
way it will be right next to your head.

Sorry Jerry, but in trying to get close to your brains, the smell is too
much, and besides, I am not inclined to stick my head there, even though
the sun don't shine there.

But, since you brought up the topic, WHAT AN ASSHOLE! I could think of
many other words -- obnoxious, egomaniac, pompous, etc. but asshole fits
the best of all.

BTW, I was correct in my response to the OP's initial request and you
were wrong. What he asked for later is irrelevant to this particular
discussion.

What he asked for later is exactly the same as what he asked for
earlier, when you examine the CONTEXT.

One of the tricks of becoming a good programmer (and a good project
manager) is to understand what the user MEANS. The vast majority of the
time it's not what he SAYS.

But you just can't admit you're too stoopid to even go back and try to
understand the context. It was very clear to me.

Jerry, the word you want was "stupid", not "stoopid". It is not the id
of a set of stairs to a building. Learn how to spell.

Not in your case. For someone with any sense at all, it's "stupid".
For you, it's "stoopid".

Quote:

There is an old saying: "Say what you mean and mean what you say". If
you go into a restaurant and want a steak medium rare, to you say
"medium" and expect the chef to read your mind as to what you _really_
wanted?

You have to learn - people don't always use the right words when
describing their needs. When you learn to understand what they MEAN,
which isn't necessarily what they SAY, then you might be on a start to
becoming a programmer.

Quote:

Anyway, I leave this thread with one word to you Mr. Unnecessarily
Nasty, who can't hold a civil conversation -- WHATEVER!

Gee, "Mr. Nasty". I haven't heard that one since third grade. But then
that seems to be about your level, so I shouldn't be surprised.

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

#36

On 9/13/2011 12:51 PM, The Natural Philosopher wrote:

Quote:

Haym Solomon wrote:

Of course. That was what he requested. Thank you for finally realizing
that the answer given was CORRECT.

There is. by Stuckle *definition*, only one *correct* answer, and it's
his, right or wrong.

I thought everybody knew that already....

And take your attitude and stuff it where the sun doesn't shine - that
way it will be right next to your head.

Sorry Jerry, but in trying to get close to your brains, the smell is
too much, and besides, I am not inclined to stick my head there, even
though the sun don't shine there.

But, since you brought up the topic, WHAT AN ASSHOLE! I could think of
many other words -- obnoxious, egomaniac, pompous, etc. but asshole
fits the best of all.

Google 'right man'

It's a known psychological syndrome.

Ah, the troll speaks. Not a programmer - just an out of work ditch
digger who doesn't want anyone to know how stoopid he really is.

TNP, if you couldn't troll or give the wrong answers (as you once again
did in this thread), you wouldn't have anything to say.

No wonder you won't use your real name. I'd be ashamed to if I were in
your shoes, also!

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to select a date that is "most recent" before another date

comp.databases.mysql comp.databases.mysql

Re: How to select a date that is "most recent" before another date - 09-13-2011 , 10:41 AM

Re: How to select a date that is "most recent" before another date - 09-13-2011 , 11:51 AM

Re: How to select a date that is "most recent" before another date - 09-13-2011 , 11:51 AM

Re: How to select a date that is "most recent" before another date - 09-13-2011 , 03:56 PM

Re: How to select a date that is "most recent" before another date - 09-13-2011 , 06:33 PM

Re: How to select a date that is "most recent" before another date - 09-13-2011 , 06:35 PM