SQL. Is there any efficient way to find second lowest value?
up vote
5
down vote
favorite
I have the following table:
ItemID Price
1 10
2 20
3 12
4 10
5 11
I need to find the second lowest price. So far, I have a query that works, but i am not sure it is the most efficient query:
select min(price)
from table
where itemid not in
(select itemid
from table
where price=
(select min(price)
from table));
What if I have to find third OR fourth minimum price? I am not even mentioning other attributes and conditions... Is there any more efficient way to do this?
PS: note that minimum is not a unique value. For example, items 1 and 4 are both minimums. Simple ordering won't do.
sql db2 subquery
asked Jun 8 '13 at 0:47
Buras
8862360106
add a comment |
up vote
5
down vote
favorite
I have the following table:
ItemID Price
1 10
2 20
3 12
4 10
5 11
I need to find the second lowest price. So far, I have a query that works, but i am not sure it is the most efficient query:
select min(price)
from table
where itemid not in
(select itemid
from table
where price=
(select min(price)
from table));
What if I have to find third OR fourth minimum price? I am not even mentioning other attributes and conditions... Is there any more efficient way to do this?
PS: note that minimum is not a unique value. For example, items 1 and 4 are both minimums. Simple ordering won't do.
sql db2 subquery
asked Jun 8 '13 at 0:47
Buras
8862360106
add a comment |
up vote
5
down vote
favorite
up vote
5
down vote
favorite
I have the following table:
ItemID Price
1 10
2 20
3 12
4 10
5 11
I need to find the second lowest price. So far, I have a query that works, but i am not sure it is the most efficient query:
select min(price)
from table
where itemid not in
(select itemid
from table
where price=
(select min(price)
from table));
What if I have to find third OR fourth minimum price? I am not even mentioning other attributes and conditions... Is there any more efficient way to do this?
PS: note that minimum is not a unique value. For example, items 1 and 4 are both minimums. Simple ordering won't do.
sql db2 subquery
asked Jun 8 '13 at 0:47
Buras
8862360106
I have the following table:
ItemID Price
1 10
2 20
3 12
4 10
5 11
I need to find the second lowest price. So far, I have a query that works, but i am not sure it is the most efficient query:
select min(price)
from table
where itemid not in
(select itemid
from table
where price=
(select min(price)
from table));
What if I have to find third OR fourth minimum price? I am not even mentioning other attributes and conditions... Is there any more efficient way to do this?
PS: note that minimum is not a unique value. For example, items 1 and 4 are both minimums. Simple ordering won't do.
sql db2 subquery
sql db2 subquery
asked Jun 8 '13 at 0:47
Buras
8862360106
asked Jun 8 '13 at 0:47
Buras
8862360106
edited Jun 8 '13 at 1:13
asked Jun 8 '13 at 0:47
Buras
8862360106
asked Jun 8 '13 at 0:47
Buras
8862360106
asked Jun 8 '13 at 0:47
Buras
8862360106
8862360106
add a comment |
add a comment |
5 Answers
5
active
oldest
votes
up vote
2
down vote
accepted
select price from table where price in (
select
distinct price
from
(select t.price,rownumber() over () as rownum from table t) as x
where x.rownum = 2 --or 3, 4, 5, etc
)
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10
– Buras
Jun 8 '13 at 1:11
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on.
– Tom Studee
Jun 8 '13 at 1:29
@TomStudee I believe the function is spelled row_number().
– WarrenT
Jun 8 '13 at 4:10
1
@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank().
– WarrenT
Jun 8 '13 at 4:31
add a comment |
up vote
4
down vote
Not sure if this would be the fastest, but it would make it easier to select the second, third, etc... Just change the TOP value.
UPDATED
SELECT MIN(price)
FROM table
WHERE price NOT IN (SELECT DISTINCT TOP 1 price FROM table ORDER BY price)
answered Jun 8 '13 at 1:00
user1845791
6802813
it is a good solution, but there might be two minimum values, and would not work
– Buras
Jun 8 '13 at 1:09
you are right, I updated my answer.
– user1845791
Jun 8 '13 at 1:28
TOP is not available in DB2
– WarrenT
Jun 8 '13 at 4:32
add a comment |
up vote
3
down vote
SELECT MIN( price )
FROM table
WHERE price > ( SELECT MIN( price )
FROM table )
edited Nov 19 at 22:31
Nick
22k71535
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
add a comment |
up vote
1
down vote
You can use order by and then select row number 2 using the method found in this answer.
edited Nov 19 at 22:31
Nick
22k71535
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
add a comment |
up vote
0
down vote
I guess a simplest way to do is using offset-fetch filter from standard sql, distinct is not necessary if you don't have repeat values in your column.
select distinct(price) from table
order by price
offset 1 row fetch first 1 row only;
no need to write complex subqueries....
In amazon redshift use limit-fetch instead for ex...
Select distinct(price) from table
order by price
limit 1
offset 1;
answered Aug 3 at 11:43
Krahul3
112
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',
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%2f16994871%2fsql-is-there-any-efficient-way-to-find-second-lowest-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
select price from table where price in (
select
distinct price
from
(select t.price,rownumber() over () as rownum from table t) as x
where x.rownum = 2 --or 3, 4, 5, etc
)
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10
– Buras
Jun 8 '13 at 1:11
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on.
– Tom Studee
Jun 8 '13 at 1:29
@TomStudee I believe the function is spelled row_number().
– WarrenT
Jun 8 '13 at 4:10
1
@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank().
– WarrenT
Jun 8 '13 at 4:31
add a comment |
up vote
2
down vote
accepted
select price from table where price in (
select
distinct price
from
(select t.price,rownumber() over () as rownum from table t) as x
where x.rownum = 2 --or 3, 4, 5, etc
)
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10
– Buras
Jun 8 '13 at 1:11
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on.
– Tom Studee
Jun 8 '13 at 1:29
@TomStudee I believe the function is spelled row_number().
– WarrenT
Jun 8 '13 at 4:10
1
@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank().
– WarrenT
Jun 8 '13 at 4:31
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
select price from table where price in (
select
distinct price
from
(select t.price,rownumber() over () as rownum from table t) as x
where x.rownum = 2 --or 3, 4, 5, etc
)
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
select price from table where price in (
select
distinct price
from
(select t.price,rownumber() over () as rownum from table t) as x
where x.rownum = 2 --or 3, 4, 5, etc
)
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
edited Jun 8 '13 at 1:28
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
answered Jun 8 '13 at 0:52
Tom Studee
8,46622938
8,46622938
it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10
– Buras
Jun 8 '13 at 1:11
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on.
– Tom Studee
Jun 8 '13 at 1:29
@TomStudee I believe the function is spelled row_number().
– WarrenT
Jun 8 '13 at 4:10
1
@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank().
– WarrenT
Jun 8 '13 at 4:31
add a comment |
it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10
– Buras
Jun 8 '13 at 1:11
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on.
– Tom Studee
Jun 8 '13 at 1:29
@TomStudee I believe the function is spelled row_number().
– WarrenT
Jun 8 '13 at 4:10
1
@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank().
– WarrenT
Jun 8 '13 at 4:31
it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10
– Buras
Jun 8 '13 at 1:11
it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10
– Buras
Jun 8 '13 at 1:11
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on.
– Tom Studee
Jun 8 '13 at 1:29
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on.
– Tom Studee
Jun 8 '13 at 1:29
@TomStudee I believe the function is spelled row_number().
– WarrenT
Jun 8 '13 at 4:10
@TomStudee I believe the function is spelled row_number().
– WarrenT
Jun 8 '13 at 4:10
1
1
@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank().
– WarrenT
Jun 8 '13 at 4:31
@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank().
– WarrenT
Jun 8 '13 at 4:31
add a comment |
up vote
4
down vote
Not sure if this would be the fastest, but it would make it easier to select the second, third, etc... Just change the TOP value.
UPDATED
SELECT MIN(price)
FROM table
WHERE price NOT IN (SELECT DISTINCT TOP 1 price FROM table ORDER BY price)
answered Jun 8 '13 at 1:00
user1845791
6802813
it is a good solution, but there might be two minimum values, and would not work
– Buras
Jun 8 '13 at 1:09
you are right, I updated my answer.
– user1845791
Jun 8 '13 at 1:28
TOP is not available in DB2
– WarrenT
Jun 8 '13 at 4:32
add a comment |
up vote
4
down vote
Not sure if this would be the fastest, but it would make it easier to select the second, third, etc... Just change the TOP value.
UPDATED
SELECT MIN(price)
FROM table
WHERE price NOT IN (SELECT DISTINCT TOP 1 price FROM table ORDER BY price)
answered Jun 8 '13 at 1:00
user1845791
6802813
it is a good solution, but there might be two minimum values, and would not work
– Buras
Jun 8 '13 at 1:09
you are right, I updated my answer.
– user1845791
Jun 8 '13 at 1:28
TOP is not available in DB2
– WarrenT
Jun 8 '13 at 4:32
add a comment |
up vote
4
down vote
up vote
4
down vote
Not sure if this would be the fastest, but it would make it easier to select the second, third, etc... Just change the TOP value.
UPDATED
SELECT MIN(price)
FROM table
WHERE price NOT IN (SELECT DISTINCT TOP 1 price FROM table ORDER BY price)
answered Jun 8 '13 at 1:00
user1845791
6802813
Not sure if this would be the fastest, but it would make it easier to select the second, third, etc... Just change the TOP value.
UPDATED
SELECT MIN(price)
FROM table
WHERE price NOT IN (SELECT DISTINCT TOP 1 price FROM table ORDER BY price)
answered Jun 8 '13 at 1:00
user1845791
6802813
edited Jun 8 '13 at 1:27
answered Jun 8 '13 at 1:00
user1845791
6802813
answered Jun 8 '13 at 1:00
user1845791
6802813
answered Jun 8 '13 at 1:00
user1845791
6802813
6802813
it is a good solution, but there might be two minimum values, and would not work
– Buras
Jun 8 '13 at 1:09
you are right, I updated my answer.
– user1845791
Jun 8 '13 at 1:28
TOP is not available in DB2
– WarrenT
Jun 8 '13 at 4:32
add a comment |
it is a good solution, but there might be two minimum values, and would not work
– Buras
Jun 8 '13 at 1:09
you are right, I updated my answer.
– user1845791
Jun 8 '13 at 1:28
TOP is not available in DB2
– WarrenT
Jun 8 '13 at 4:32
it is a good solution, but there might be two minimum values, and would not work
– Buras
Jun 8 '13 at 1:09
it is a good solution, but there might be two minimum values, and would not work
– Buras
Jun 8 '13 at 1:09
you are right, I updated my answer.
– user1845791
Jun 8 '13 at 1:28
you are right, I updated my answer.
– user1845791
Jun 8 '13 at 1:28
TOP is not available in DB2
– WarrenT
Jun 8 '13 at 4:32
TOP is not available in DB2
– WarrenT
Jun 8 '13 at 4:32
add a comment |
up vote
3
down vote
SELECT MIN( price )
FROM table
WHERE price > ( SELECT MIN( price )
FROM table )
edited Nov 19 at 22:31
Nick
22k71535
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
add a comment |
up vote
3
down vote
SELECT MIN( price )
FROM table
WHERE price > ( SELECT MIN( price )
FROM table )
edited Nov 19 at 22:31
Nick
22k71535
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
add a comment |
up vote
3
down vote
up vote
3
down vote
SELECT MIN( price )
FROM table
WHERE price > ( SELECT MIN( price )
FROM table )
edited Nov 19 at 22:31
Nick
22k71535
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
SELECT MIN( price )
FROM table
WHERE price > ( SELECT MIN( price )
FROM table )
edited Nov 19 at 22:31
Nick
22k71535
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
edited Nov 19 at 22:31
Nick
22k71535
edited Nov 19 at 22:31
Nick
22k71535
edited Nov 19 at 22:31
Nick
22k71535
22k71535
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
answered Nov 8 '13 at 7:49
Hashid Hameed
668521
668521
add a comment |
add a comment |
up vote
1
down vote
You can use order by and then select row number 2 using the method found in this answer.
edited Nov 19 at 22:31
Nick
22k71535
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
add a comment |
up vote
1
down vote
You can use order by and then select row number 2 using the method found in this answer.
edited Nov 19 at 22:31
Nick
22k71535
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
add a comment |
up vote
1
down vote
up vote
1
down vote
You can use order by and then select row number 2 using the method found in this answer.
edited Nov 19 at 22:31
Nick
22k71535
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
You can use order by and then select row number 2 using the method found in this answer.
edited Nov 19 at 22:31
Nick
22k71535
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
edited Nov 19 at 22:31
Nick
22k71535
edited Nov 19 at 22:31
Nick
22k71535
edited Nov 19 at 22:31
Nick
22k71535
22k71535
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
answered Jun 8 '13 at 0:56
Dan Albert
5,01211447
5,01211447
add a comment |
add a comment |
up vote
0
down vote
I guess a simplest way to do is using offset-fetch filter from standard sql, distinct is not necessary if you don't have repeat values in your column.
select distinct(price) from table
order by price
offset 1 row fetch first 1 row only;
no need to write complex subqueries....
In amazon redshift use limit-fetch instead for ex...
Select distinct(price) from table
order by price
limit 1
offset 1;
answered Aug 3 at 11:43
Krahul3
112
add a comment |
up vote
0
down vote
I guess a simplest way to do is using offset-fetch filter from standard sql, distinct is not necessary if you don't have repeat values in your column.
select distinct(price) from table
order by price
offset 1 row fetch first 1 row only;
no need to write complex subqueries....
In amazon redshift use limit-fetch instead for ex...
Select distinct(price) from table
order by price
limit 1
offset 1;
answered Aug 3 at 11:43
Krahul3
112
add a comment |
up vote
0
down vote
up vote
0
down vote
I guess a simplest way to do is using offset-fetch filter from standard sql, distinct is not necessary if you don't have repeat values in your column.
select distinct(price) from table
order by price
offset 1 row fetch first 1 row only;
no need to write complex subqueries....
In amazon redshift use limit-fetch instead for ex...
Select distinct(price) from table
order by price
limit 1
offset 1;
answered Aug 3 at 11:43
Krahul3
112
I guess a simplest way to do is using offset-fetch filter from standard sql, distinct is not necessary if you don't have repeat values in your column.
select distinct(price) from table
order by price
offset 1 row fetch first 1 row only;
no need to write complex subqueries....
In amazon redshift use limit-fetch instead for ex...
Select distinct(price) from table
order by price
limit 1
offset 1;
answered Aug 3 at 11:43
Krahul3
112
answered Aug 3 at 11:43
Krahul3
112
answered Aug 3 at 11:43
Krahul3
112
answered Aug 3 at 11:43
Krahul3
112
112
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f16994871%2fsql-is-there-any-efficient-way-to-find-second-lowest-value%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
