[SQL] Select the second to last date
harry_le_ravi
-
Airmanbzh Posted messages 122 Status Member -
Airmanbzh Posted messages 122 Status Member -
Hello everyone,
I am looking for 2 SQL queries that will allow me to retrieve:
- the value of a field a where the date field is greater than all others
[CODE]Select table.a FROM table WHERE table.date > ALL[/CODE]
- the value of a field a where the date field corresponds to the second to last date (that is just before the previous query)
Could you tell me if the first query is correct and the solution to the second?
Thank you in advance.
I am looking for 2 SQL queries that will allow me to retrieve:
- the value of a field a where the date field is greater than all others
[CODE]Select table.a FROM table WHERE table.date > ALL[/CODE]
- the value of a field a where the date field corresponds to the second to last date (that is just before the previous query)
Could you tell me if the first query is correct and the solution to the second?
Thank you in advance.
Configuration: Windows XP Internet Explorer 7.0
9 answers
Oh yes!
I hadn't studied LIMIT.
It seems pretty good to me.
I select the values from the field a in descending date order and I only display the first two results.
However, in terms of optimization, it might use quite a bit of resources if I have to select all the values of a from the table beforehand?
I'm just curious, though.
Thank you.
I hadn't studied LIMIT.
It seems pretty good to me.
I select the values from the field a in descending date order and I only display the first two results.
However, in terms of optimization, it might use quite a bit of resources if I have to select all the values of a from the table beforehand?
I'm just curious, though.
Thank you.
You are offered this for the first query:
SELECT TABLE.a
FROM TABLE
WHERE TABLE.date = (SELECT max(TABLE.date) FROM TABLE);
For the second, you are offered this:
SELECT TABLE.a
FROM TABLE
WHERE TABLE.date = (SELECT max(TABLE.date) FROM TABLE WHERE date < (SELECT max(date) FROM TABLE));
SELECT TABLE.a
FROM TABLE
WHERE TABLE.date = (SELECT max(TABLE.date) FROM TABLE);
For the second, you are offered this:
SELECT TABLE.a
FROM TABLE
WHERE TABLE.date = (SELECT max(TABLE.date) FROM TABLE WHERE date < (SELECT max(date) FROM TABLE));
Yes, indeed, I hadn't noticed that it was a different field than the date field that you wanted to retrieve.
The first one seems correct to me. Try testing without the second select (for optimization reasons)
I don't know if this works or not in this way, but it's worth trying ;-)
For the second one, there might be an error because your nested query returns multiple results
However, I just thought of something:
A single line query returns 2 lines. The first with field a corresponding to the largest date and a second with field a corresponding to the next date (so the penultimate).
More optimized and less complicated than all the ones we proposed ;-).
And if you want 2 queries, you can do:
--
A closed topic is a time saver for everyone, so remember to close them.
The first one seems correct to me. Try testing without the second select (for optimization reasons)
SELECT TABLE.a FROM TABLE WHERE TABLE.date = max(TABLE.date);
I don't know if this works or not in this way, but it's worth trying ;-)
For the second one, there might be an error because your nested query returns multiple results
SELECT TABLE.a FROM TABLE WHERE TABLE.date = (SELECT max(TABLE.date) FROM TABLE WHERE date < (SELECT max(date) FROM TABLE));
However, I just thought of something:
SELECT table.a FROM table ORDER BY table.date DESC LIMIT 0,2;
A single line query returns 2 lines. The first with field a corresponding to the largest date and a second with field a corresponding to the next date (so the penultimate).
More optimized and less complicated than all the ones we proposed ;-).
And if you want 2 queries, you can do:
// largest date SELECT table.a FROM table ORDER BY table.date DESC LIMIT 0,1; // second largest date SELECT table.a FROM table ORDER BY table.date DESC LIMIT 1,1;
--
A closed topic is a time saver for everyone, so remember to close them.
In fact, MySQL will retrieve all the rows from your table and sort them, so it will be an algorithm with a complexity of "2n" given MySQL's processing algorithm.
So it's better to do this than to run 2 selects in the same query with a filter on the data.
For 5000 lines of data:
With ... LIMIT ... : 2n
Nested queries: n^2 + 5000*(the number of filters)
Thus, it is a gain in time and processor calculations.
--
A closed thread is a time saver for everyone, so remember to close them.
So it's better to do this than to run 2 selects in the same query with a filter on the data.
For 5000 lines of data:
With ... LIMIT ... : 2n
Nested queries: n^2 + 5000*(the number of filters)
Thus, it is a gain in time and processor calculations.
--
A closed thread is a time saver for everyone, so remember to close them.
Hello
you can use two queries
in the first one, you take only the first two records sorted by ascending date
and in the second one, you take the max date from the first query.
or you can, I think, do the manipulation with a single query and apply the same thing.
you can use two queries
in the first one, you take only the first two records sorted by ascending date
and in the second one, you take the max date from the first query.
or you can, I think, do the manipulation with a single query and apply the same thing.
I'm sorry, I don't have SQL on my PC, but I will install it tonight. If you want, I can give you the Transact code.
For the SQL version, I would have done something like this:
And I think you can retrieve your second to last date using a function like:
Explanations: you retrieve the date with the minimum number of days between the maximum date and a date Y. The condition allows you to filter that the returned date is not the maximum date.
I hope this will solve your problem.
Best regards
--
A closed topic is a time saver for everyone, so remember to close them.
SELECT MAX(table.date) FROM table
And I think you can retrieve your second to last date using a function like:
SELECT MIN(MAX(table.date) - table.date) FROM table WHERE TO_DAYS(MAX(table.date) - table.date) >0;
Explanations: you retrieve the date with the minimum number of days between the maximum date and a date Y. The condition allows you to filter that the returned date is not the maximum date.
I hope this will solve your problem.
Best regards
--
A closed topic is a time saver for everyone, so remember to close them.