Taking the record with the max dateOracle analytic function - using FIRST_VALUE to remove unwanted rowsSelect distinct rows with max date with repeated and null values (Oracle)Pagination using an array in CakePHPOracle, If-else condition in where clauseHow to return only the Date from a SQL Server DateTime datatypeCompare two dates with JavaScriptWhere can I find documentation on formatting a date in JavaScript?Return record with max dateDetecting an “invalid date” Date instance in JavaScriptHow do I get the current date in JavaScript?Calculate difference between two dates (number of days)?How to format a JavaScript dateGet current time and date on Androidselect by max id into max date
It grows, but water kills it
Problem with TransformedDistribution
How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?
What if a revenant (monster) gains fire resistance?
How to implement a feedback to keep the DC gain at zero for this conceptual passive filter?
Start making guitar arrangements
The screen of my macbook suddenly broken down how can I do to recover
Offered money to buy a house, seller is asking for more to cover gap between their listing and mortgage owed
What is the evidence for the "tyranny of the majority problem" in a direct democracy context?
On a tidally locked planet, would time be quantized?
Multiplicative persistence
Not using 's' for he/she/it
Fear of getting stuck on one programming language / technology that is not used in my country
A social experiment. What is the worst that can happen?
What are the purposes of autoencoders?
What does chmod -u do?
Approximating irrational number to rational number
Removing files under particular conditions (number of files, file age)
Calculating Wattage for Resistor in High Frequency Application?
Create all possible words using a set or letters
Open a doc from terminal, but not by its name
Aragorn's "guise" in the Orthanc Stone
Closed-form expression for certain product
Travelling outside the UK without a passport
Taking the record with the max date
Oracle analytic function - using FIRST_VALUE to remove unwanted rowsSelect distinct rows with max date with repeated and null values (Oracle)Pagination using an array in CakePHPOracle, If-else condition in where clauseHow to return only the Date from a SQL Server DateTime datatypeCompare two dates with JavaScriptWhere can I find documentation on formatting a date in JavaScript?Return record with max dateDetecting an “invalid date” Date instance in JavaScriptHow do I get the current date in JavaScript?Calculate difference between two dates (number of days)?How to format a JavaScript dateGet current time and date on Androidselect by max id into max date
Let's assume I extract some set of data.
i.e.
SELECT A, date
FROM table
I want just the record with the max date (for each value of A). I could write
SELECT A, col_date
FROM TABLENAME t_ext
WHERE col_date = (SELECT MAX (col_date)
FROM TABLENAME t_in
WHERE t_in.A = t_ext.A)
But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?
oracle date max analytic-functions
edited 2 days ago
ZygD
4,259113053
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
add a comment |
Let's assume I extract some set of data.
i.e.
SELECT A, date
FROM table
I want just the record with the max date (for each value of A). I could write
SELECT A, col_date
FROM TABLENAME t_ext
WHERE col_date = (SELECT MAX (col_date)
FROM TABLENAME t_in
WHERE t_in.A = t_ext.A)
But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?
oracle date max analytic-functions
edited 2 days ago
ZygD
4,259113053
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
add a comment |
Let's assume I extract some set of data.
i.e.
SELECT A, date
FROM table
I want just the record with the max date (for each value of A). I could write
SELECT A, col_date
FROM TABLENAME t_ext
WHERE col_date = (SELECT MAX (col_date)
FROM TABLENAME t_in
WHERE t_in.A = t_ext.A)
But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?
oracle date max analytic-functions
edited 2 days ago
ZygD
4,259113053
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
Let's assume I extract some set of data.
i.e.
SELECT A, date
FROM table
I want just the record with the max date (for each value of A). I could write
SELECT A, col_date
FROM TABLENAME t_ext
WHERE col_date = (SELECT MAX (col_date)
FROM TABLENAME t_in
WHERE t_in.A = t_ext.A)
But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?
oracle date max analytic-functions
oracle date max analytic-functions
edited 2 days ago
ZygD
4,259113053
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
edited 2 days ago
ZygD
4,259113053
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
edited 2 days ago
ZygD
4,259113053
edited 2 days ago
ZygD
4,259113053
edited 2 days ago
ZygD
4,259113053
4,259113053
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
asked Jan 17 '12 at 16:19
ReviousRevious
2,5162272116
2,5162272116
add a comment |
add a comment |
6 Answers
6
active
oldest
votes
The analytic function approach would look something like
SELECT a, some_date_column
FROM (SELECT a,
some_date_column,
rank() over (partition by a order by some_date_column desc) rnk
FROM tablename)
WHERE rnk = 1
Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
1
What do you mean with ties?
– Revious
Jan 18 '12 at 13:37
2
@Gik25 - A tie would occur if there were, say, two rows inTABLENAMEthat had the same value forAand the same value forSOME_DATE_COLUMN. Your original query would return both of those rows as would mine. If, on the other hand, you used theROW_NUMBERfunction, only one of the two rows would be returned (though the choice of which row to return would be arbitrary).
– Justin Cave
Jan 18 '12 at 14:45
add a comment |
If date and col_date are the same columns you should simply do:
SELECT A, MAX(date) FROM t GROUP BY A
Why not use:
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME )
SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
Otherwise:
SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)
FROM TABLENAME
GROUP BY A
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
1
+1 The first SQL is the simplest and the best answer
– Matt Donnan
Jan 17 '12 at 16:38
3
@Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question).
– ypercubeᵀᴹ
Jan 17 '12 at 16:39
@ypercube I agree, and that is what the question looks like
– Matt Donnan
Jan 17 '12 at 16:41
I've couln't make third query work in my Oracle 11g. And second query is missing a group by clause. It should be:WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
– EAmez
Oct 8 '18 at 12:52
add a comment |
You could also use:
SELECT t.*
FROM
TABLENAME t
JOIN
( SELECT A, MAX(col_date) AS col_date
FROM TABLENAME
GROUP BY A
) m
ON m.A = t.A
AND m.col_date = t.col_date
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
1
This would be a good choice if there was an index on(a, col_date), especially if there are lots of dates for each distinct value of A.
– APC
Jan 17 '12 at 18:17
This works for me.
– GunWanderer
Mar 13 '17 at 20:00
add a comment |
A is the key, max(date) is the value, we might simplify the query as below:
SELECT distinct A, max(date) over (partition by A)
FROM TABLENAME
edited 2 days ago
ZygD
4,259113053
answered Dec 7 '17 at 6:34
user2778168user2778168
194
add a comment |
Justin Cave answer is the best, but if you want antoher option, try this:
select A,col_date
from (select A,col_date
from tablename
order by col_date desc)
where rownum<2
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
1
thank you so much
– Ras Rass
Sep 27 '17 at 4:52
add a comment |
SELECT mu_file, mudate
FROM flightdata t_ext
WHERE mudate = (SELECT MAX (mudate)
FROM flightdata where mudate < sysdate)
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
answered Jun 26 '15 at 2:02
RobertRobert
1
This doesn't look like it matches the question?
– Nathan Tuggy
Jun 26 '15 at 2:24
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f8898020%2ftaking-the-record-with-the-max-date%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
6 Answers
6
active
oldest
votes
6 Answers
6
active
oldest
votes
active
oldest
votes
active
oldest
votes
The analytic function approach would look something like
SELECT a, some_date_column
FROM (SELECT a,
some_date_column,
rank() over (partition by a order by some_date_column desc) rnk
FROM tablename)
WHERE rnk = 1
Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
1
What do you mean with ties?
– Revious
Jan 18 '12 at 13:37
2
@Gik25 - A tie would occur if there were, say, two rows inTABLENAMEthat had the same value forAand the same value forSOME_DATE_COLUMN. Your original query would return both of those rows as would mine. If, on the other hand, you used theROW_NUMBERfunction, only one of the two rows would be returned (though the choice of which row to return would be arbitrary).
– Justin Cave
Jan 18 '12 at 14:45
add a comment |
The analytic function approach would look something like
SELECT a, some_date_column
FROM (SELECT a,
some_date_column,
rank() over (partition by a order by some_date_column desc) rnk
FROM tablename)
WHERE rnk = 1
Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
1
What do you mean with ties?
– Revious
Jan 18 '12 at 13:37
2
@Gik25 - A tie would occur if there were, say, two rows inTABLENAMEthat had the same value forAand the same value forSOME_DATE_COLUMN. Your original query would return both of those rows as would mine. If, on the other hand, you used theROW_NUMBERfunction, only one of the two rows would be returned (though the choice of which row to return would be arbitrary).
– Justin Cave
Jan 18 '12 at 14:45
add a comment |
The analytic function approach would look something like
SELECT a, some_date_column
FROM (SELECT a,
some_date_column,
rank() over (partition by a order by some_date_column desc) rnk
FROM tablename)
WHERE rnk = 1
Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
The analytic function approach would look something like
SELECT a, some_date_column
FROM (SELECT a,
some_date_column,
rank() over (partition by a order by some_date_column desc) rnk
FROM tablename)
WHERE rnk = 1
Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
answered Jan 17 '12 at 16:26
Justin CaveJustin Cave
189k18289323
189k18289323
1
What do you mean with ties?
– Revious
Jan 18 '12 at 13:37
2
@Gik25 - A tie would occur if there were, say, two rows inTABLENAMEthat had the same value forAand the same value forSOME_DATE_COLUMN. Your original query would return both of those rows as would mine. If, on the other hand, you used theROW_NUMBERfunction, only one of the two rows would be returned (though the choice of which row to return would be arbitrary).
– Justin Cave
Jan 18 '12 at 14:45
add a comment |
1
What do you mean with ties?
– Revious
Jan 18 '12 at 13:37
2
@Gik25 - A tie would occur if there were, say, two rows inTABLENAMEthat had the same value forAand the same value forSOME_DATE_COLUMN. Your original query would return both of those rows as would mine. If, on the other hand, you used theROW_NUMBERfunction, only one of the two rows would be returned (though the choice of which row to return would be arbitrary).
– Justin Cave
Jan 18 '12 at 14:45
1
1
What do you mean with ties?
– Revious
Jan 18 '12 at 13:37
What do you mean with ties?
– Revious
Jan 18 '12 at 13:37
2
2
@Gik25 - A tie would occur if there were, say, two rows in
TABLENAME that had the same value for A and the same value for SOME_DATE_COLUMN. Your original query would return both of those rows as would mine. If, on the other hand, you used the ROW_NUMBER function, only one of the two rows would be returned (though the choice of which row to return would be arbitrary).– Justin Cave
Jan 18 '12 at 14:45
@Gik25 - A tie would occur if there were, say, two rows in
TABLENAME that had the same value for A and the same value for SOME_DATE_COLUMN. Your original query would return both of those rows as would mine. If, on the other hand, you used the ROW_NUMBER function, only one of the two rows would be returned (though the choice of which row to return would be arbitrary).– Justin Cave
Jan 18 '12 at 14:45
add a comment |
If date and col_date are the same columns you should simply do:
SELECT A, MAX(date) FROM t GROUP BY A
Why not use:
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME )
SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
Otherwise:
SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)
FROM TABLENAME
GROUP BY A
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
1
+1 The first SQL is the simplest and the best answer
– Matt Donnan
Jan 17 '12 at 16:38
3
@Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question).
– ypercubeᵀᴹ
Jan 17 '12 at 16:39
@ypercube I agree, and that is what the question looks like
– Matt Donnan
Jan 17 '12 at 16:41
I've couln't make third query work in my Oracle 11g. And second query is missing a group by clause. It should be:WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
– EAmez
Oct 8 '18 at 12:52
add a comment |
If date and col_date are the same columns you should simply do:
SELECT A, MAX(date) FROM t GROUP BY A
Why not use:
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME )
SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
Otherwise:
SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)
FROM TABLENAME
GROUP BY A
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
1
+1 The first SQL is the simplest and the best answer
– Matt Donnan
Jan 17 '12 at 16:38
3
@Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question).
– ypercubeᵀᴹ
Jan 17 '12 at 16:39
@ypercube I agree, and that is what the question looks like
– Matt Donnan
Jan 17 '12 at 16:41
I've couln't make third query work in my Oracle 11g. And second query is missing a group by clause. It should be:WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
– EAmez
Oct 8 '18 at 12:52
add a comment |
If date and col_date are the same columns you should simply do:
SELECT A, MAX(date) FROM t GROUP BY A
Why not use:
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME )
SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
Otherwise:
SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)
FROM TABLENAME
GROUP BY A
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
If date and col_date are the same columns you should simply do:
SELECT A, MAX(date) FROM t GROUP BY A
Why not use:
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME )
SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
Otherwise:
SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)
FROM TABLENAME
GROUP BY A
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
answered Jan 17 '12 at 16:24
BenoitBenoit
59.8k15170213
59.8k15170213
1
+1 The first SQL is the simplest and the best answer
– Matt Donnan
Jan 17 '12 at 16:38
3
@Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question).
– ypercubeᵀᴹ
Jan 17 '12 at 16:39
@ypercube I agree, and that is what the question looks like
– Matt Donnan
Jan 17 '12 at 16:41
I've couln't make third query work in my Oracle 11g. And second query is missing a group by clause. It should be:WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
– EAmez
Oct 8 '18 at 12:52
add a comment |
1
+1 The first SQL is the simplest and the best answer
– Matt Donnan
Jan 17 '12 at 16:38
3
@Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question).
– ypercubeᵀᴹ
Jan 17 '12 at 16:39
@ypercube I agree, and that is what the question looks like
– Matt Donnan
Jan 17 '12 at 16:41
I've couln't make third query work in my Oracle 11g. And second query is missing a group by clause. It should be:WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date
– EAmez
Oct 8 '18 at 12:52
1
1
+1 The first SQL is the simplest and the best answer
– Matt Donnan
Jan 17 '12 at 16:38
+1 The first SQL is the simplest and the best answer
– Matt Donnan
Jan 17 '12 at 16:38
3
3
@Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question).
– ypercubeᵀᴹ
Jan 17 '12 at 16:39
@Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question).
– ypercubeᵀᴹ
Jan 17 '12 at 16:39
@ypercube I agree, and that is what the question looks like
– Matt Donnan
Jan 17 '12 at 16:41
@ypercube I agree, and that is what the question looks like
– Matt Donnan
Jan 17 '12 at 16:41
I've couln't make third query work in my Oracle 11g. And second query is missing a group by clause. It should be:
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date– EAmez
Oct 8 '18 at 12:52
I've couln't make third query work in my Oracle 11g. And second query is missing a group by clause. It should be:
WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date– EAmez
Oct 8 '18 at 12:52
add a comment |
You could also use:
SELECT t.*
FROM
TABLENAME t
JOIN
( SELECT A, MAX(col_date) AS col_date
FROM TABLENAME
GROUP BY A
) m
ON m.A = t.A
AND m.col_date = t.col_date
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
1
This would be a good choice if there was an index on(a, col_date), especially if there are lots of dates for each distinct value of A.
– APC
Jan 17 '12 at 18:17
This works for me.
– GunWanderer
Mar 13 '17 at 20:00
add a comment |
You could also use:
SELECT t.*
FROM
TABLENAME t
JOIN
( SELECT A, MAX(col_date) AS col_date
FROM TABLENAME
GROUP BY A
) m
ON m.A = t.A
AND m.col_date = t.col_date
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
1
This would be a good choice if there was an index on(a, col_date), especially if there are lots of dates for each distinct value of A.
– APC
Jan 17 '12 at 18:17
This works for me.
– GunWanderer
Mar 13 '17 at 20:00
add a comment |
You could also use:
SELECT t.*
FROM
TABLENAME t
JOIN
( SELECT A, MAX(col_date) AS col_date
FROM TABLENAME
GROUP BY A
) m
ON m.A = t.A
AND m.col_date = t.col_date
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
You could also use:
SELECT t.*
FROM
TABLENAME t
JOIN
( SELECT A, MAX(col_date) AS col_date
FROM TABLENAME
GROUP BY A
) m
ON m.A = t.A
AND m.col_date = t.col_date
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
answered Jan 17 '12 at 16:37
ypercubeᵀᴹypercubeᵀᴹ
94.4k11140199
94.4k11140199
1
This would be a good choice if there was an index on(a, col_date), especially if there are lots of dates for each distinct value of A.
– APC
Jan 17 '12 at 18:17
This works for me.
– GunWanderer
Mar 13 '17 at 20:00
add a comment |
1
This would be a good choice if there was an index on(a, col_date), especially if there are lots of dates for each distinct value of A.
– APC
Jan 17 '12 at 18:17
This works for me.
– GunWanderer
Mar 13 '17 at 20:00
1
1
This would be a good choice if there was an index on
(a, col_date), especially if there are lots of dates for each distinct value of A.– APC
Jan 17 '12 at 18:17
This would be a good choice if there was an index on
(a, col_date), especially if there are lots of dates for each distinct value of A.– APC
Jan 17 '12 at 18:17
This works for me.
– GunWanderer
Mar 13 '17 at 20:00
This works for me.
– GunWanderer
Mar 13 '17 at 20:00
add a comment |
A is the key, max(date) is the value, we might simplify the query as below:
SELECT distinct A, max(date) over (partition by A)
FROM TABLENAME
edited 2 days ago
ZygD
4,259113053
answered Dec 7 '17 at 6:34
user2778168user2778168
194
add a comment |
A is the key, max(date) is the value, we might simplify the query as below:
SELECT distinct A, max(date) over (partition by A)
FROM TABLENAME
edited 2 days ago
ZygD
4,259113053
answered Dec 7 '17 at 6:34
user2778168user2778168
194
add a comment |
A is the key, max(date) is the value, we might simplify the query as below:
SELECT distinct A, max(date) over (partition by A)
FROM TABLENAME
edited 2 days ago
ZygD
4,259113053
answered Dec 7 '17 at 6:34
user2778168user2778168
194
A is the key, max(date) is the value, we might simplify the query as below:
SELECT distinct A, max(date) over (partition by A)
FROM TABLENAME
edited 2 days ago
ZygD
4,259113053
answered Dec 7 '17 at 6:34
user2778168user2778168
194
edited 2 days ago
ZygD
4,259113053
edited 2 days ago
ZygD
4,259113053
edited 2 days ago
ZygD
4,259113053
4,259113053
answered Dec 7 '17 at 6:34
user2778168user2778168
194
answered Dec 7 '17 at 6:34
user2778168user2778168
194
answered Dec 7 '17 at 6:34
user2778168user2778168
194
194
add a comment |
add a comment |
Justin Cave answer is the best, but if you want antoher option, try this:
select A,col_date
from (select A,col_date
from tablename
order by col_date desc)
where rownum<2
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
1
thank you so much
– Ras Rass
Sep 27 '17 at 4:52
add a comment |
Justin Cave answer is the best, but if you want antoher option, try this:
select A,col_date
from (select A,col_date
from tablename
order by col_date desc)
where rownum<2
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
1
thank you so much
– Ras Rass
Sep 27 '17 at 4:52
add a comment |
Justin Cave answer is the best, but if you want antoher option, try this:
select A,col_date
from (select A,col_date
from tablename
order by col_date desc)
where rownum<2
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
Justin Cave answer is the best, but if you want antoher option, try this:
select A,col_date
from (select A,col_date
from tablename
order by col_date desc)
where rownum<2
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
answered Jan 19 '16 at 11:36
AitorAitor
2,1431828
2,1431828
1
thank you so much
– Ras Rass
Sep 27 '17 at 4:52
add a comment |
1
thank you so much
– Ras Rass
Sep 27 '17 at 4:52
1
1
thank you so much
– Ras Rass
Sep 27 '17 at 4:52
thank you so much
– Ras Rass
Sep 27 '17 at 4:52
add a comment |
SELECT mu_file, mudate
FROM flightdata t_ext
WHERE mudate = (SELECT MAX (mudate)
FROM flightdata where mudate < sysdate)
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
answered Jun 26 '15 at 2:02
RobertRobert
1
This doesn't look like it matches the question?
– Nathan Tuggy
Jun 26 '15 at 2:24
add a comment |
SELECT mu_file, mudate
FROM flightdata t_ext
WHERE mudate = (SELECT MAX (mudate)
FROM flightdata where mudate < sysdate)
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
answered Jun 26 '15 at 2:02
RobertRobert
1
This doesn't look like it matches the question?
– Nathan Tuggy
Jun 26 '15 at 2:24
add a comment |
SELECT mu_file, mudate
FROM flightdata t_ext
WHERE mudate = (SELECT MAX (mudate)
FROM flightdata where mudate < sysdate)
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
answered Jun 26 '15 at 2:02
RobertRobert
1
SELECT mu_file, mudate
FROM flightdata t_ext
WHERE mudate = (SELECT MAX (mudate)
FROM flightdata where mudate < sysdate)
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
answered Jun 26 '15 at 2:02
RobertRobert
1
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
edited Jun 26 '15 at 2:58
josliber♦
37.5k1165104
37.5k1165104
answered Jun 26 '15 at 2:02
RobertRobert
1
answered Jun 26 '15 at 2:02
RobertRobert
1
answered Jun 26 '15 at 2:02
RobertRobert
1
1
This doesn't look like it matches the question?
– Nathan Tuggy
Jun 26 '15 at 2:24
add a comment |
This doesn't look like it matches the question?
– Nathan Tuggy
Jun 26 '15 at 2:24
This doesn't look like it matches the question?
– Nathan Tuggy
Jun 26 '15 at 2:24
This doesn't look like it matches the question?
– Nathan Tuggy
Jun 26 '15 at 2:24
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f8898020%2ftaking-the-record-with-the-max-date%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
