Filling a column with incremental numbersINNER JOIN between 2 tables vs WHERE on one table performanceHow to...
How should I handle players who ignore the session zero agreement?
How to change a n value for a value like X
What is the most fuel efficient way out of the Solar System?
kill -0 <PID> は何をするのでしょうか？
use of 4/2 chord more compelling than root position?
Is there a weight limit to Feather Fall?
Use two 8s and two 3s to make the number 24
Why don't hotels mount air conditioning units outside the rooms?
In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?
Why am I able to open Wireshark and capture packets in macOS without root privileges?
Early credit roll before the end of the film
Do authors have to be politically correct in article-writing?
Why are the books in the Game of Thrones citadel library shelved spine inwards?
What is the difference between rolling more dice versus fewer dice?
How can a school be getting an epidemic of whooping cough if most of the students are vaccinated?
Consequences of lack of rigour
Calculate the number of points of an elliptic curve in medium Weierstrass form over finite field
Salesforce package error error “You can't specify version for namespace sf_com_apps because this namespace is not installed to your organization.”
A curious equality of integrals involving the prime counting function?
Nested word series [humans only]
What incentives do banks have to gather up loans into pools (backed by Ginnie Mae)and selling them?
How do you funnel food off a cutting board?
Cookies - Should the toggles be on?
Absorbing damage with Planeswalker
Filling a column with incremental numbers
INNER JOIN between 2 tables vs WHERE on one table performanceHow to sort category by path and sort order?update column based on the sort order of another queryInner join returns duplicate resultsWhat is wrong with this mysql trigger?MySQL: Split column by delimiter & replace numbers with values from another columnMySQL: Loop on each column value & split column by delimiter & replace numbers with values from another columnReplace Subquery with JOIN - MYSQLMySQL get previous row with inner joinFind MySQL InnoDB index corruption
I got an SQL query like this:
SELECT o.option_id,v.value,o.sort_order
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND o.attribute_id=167
ORDER BY v.value;
The result is:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 3 |
| 66 | m | 5 |
| 67 | s | 7 |
| 238 | x | 5 |
| 68 | z | 6 |
+-----------+-------+------------+
I need to update the sort_order, filling with numbers from 1 to N,
based on the alphabetical order of the value field.
Is it possible with SQL only? The result after should be:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 4 |
| 66 | m | 5 |
| 67 | s | 6 |
| 238 | x | 7 |
| 68 | z | 8 |
+-----------+-------+------------+
mysql
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I got an SQL query like this:
SELECT o.option_id,v.value,o.sort_order
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND o.attribute_id=167
ORDER BY v.value;
The result is:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 3 |
| 66 | m | 5 |
| 67 | s | 7 |
| 238 | x | 5 |
| 68 | z | 6 |
+-----------+-------+------------+
I need to update the sort_order, filling with numbers from 1 to N,
based on the alphabetical order of the value field.
Is it possible with SQL only? The result after should be:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 4 |
| 66 | m | 5 |
| 67 | s | 6 |
| 238 | x | 7 |
| 68 | z | 8 |
+-----------+-------+------------+
mysql
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I got an SQL query like this:
SELECT o.option_id,v.value,o.sort_order
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND o.attribute_id=167
ORDER BY v.value;
The result is:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 3 |
| 66 | m | 5 |
| 67 | s | 7 |
| 238 | x | 5 |
| 68 | z | 6 |
+-----------+-------+------------+
I need to update the sort_order, filling with numbers from 1 to N,
based on the alphabetical order of the value field.
Is it possible with SQL only? The result after should be:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 4 |
| 66 | m | 5 |
| 67 | s | 6 |
| 238 | x | 7 |
| 68 | z | 8 |
+-----------+-------+------------+
mysql
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
I got an SQL query like this:
SELECT o.option_id,v.value,o.sort_order
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND o.attribute_id=167
ORDER BY v.value;
The result is:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 3 |
| 66 | m | 5 |
| 67 | s | 7 |
| 238 | x | 5 |
| 68 | z | 6 |
+-----------+-------+------------+
I need to update the sort_order, filling with numbers from 1 to N,
based on the alphabetical order of the value field.
Is it possible with SQL only? The result after should be:
+-----------+-------+------------+
| option_id | value | sort_order |
+-----------+-------+------------+
| 320 | a | 1 |
| 64 | b | 2 |
| 444 | c | 3 |
| 65 | d | 4 |
| 66 | m | 5 |
| 67 | s | 6 |
| 238 | x | 7 |
| 68 | z | 8 |
+-----------+-------+------------+
mysql
mysql
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
edited Mar 13 '15 at 6:37
Artisan72
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
asked Mar 12 '15 at 16:59
Artisan72Artisan72
1113
1113
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Try this :
SET @row_number = 0;
SELECT o.option_id,v.value,o.sort_order, (@row_number:=@row_number + 1) AS NewOrder
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND attribute_id=167
ORDER BY v.value;
The result will be:
+-----------+-------+------------+------------+
| option_id | value | sort_order | NewOrder |
+-----------+-------+------------+------------+
| 320 | a | 1 | 1 |
| 64 | b | 2 | 2 |
| 444 | c | 3 | 3 |
| 65 | d | 3 | 4 |
| 66 | m | 5 | 5 |
| 67 | s | 7 | 6 |
| 238 | x | 5 | 7 |
| 68 | z | 6 | 8 |
+-----------+-------+------------+------------+
You have just to transform the query to perform update instead of select.
answered Mar 12 '15 at 20:47
NicoNico
8721716
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f95094%2ffilling-a-column-with-incremental-numbers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try this :
SET @row_number = 0;
SELECT o.option_id,v.value,o.sort_order, (@row_number:=@row_number + 1) AS NewOrder
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND attribute_id=167
ORDER BY v.value;
The result will be:
+-----------+-------+------------+------------+
| option_id | value | sort_order | NewOrder |
+-----------+-------+------------+------------+
| 320 | a | 1 | 1 |
| 64 | b | 2 | 2 |
| 444 | c | 3 | 3 |
| 65 | d | 3 | 4 |
| 66 | m | 5 | 5 |
| 67 | s | 7 | 6 |
| 238 | x | 5 | 7 |
| 68 | z | 6 | 8 |
+-----------+-------+------------+------------+
You have just to transform the query to perform update instead of select.
answered Mar 12 '15 at 20:47
NicoNico
8721716
add a comment |
Try this :
SET @row_number = 0;
SELECT o.option_id,v.value,o.sort_order, (@row_number:=@row_number + 1) AS NewOrder
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND attribute_id=167
ORDER BY v.value;
The result will be:
+-----------+-------+------------+------------+
| option_id | value | sort_order | NewOrder |
+-----------+-------+------------+------------+
| 320 | a | 1 | 1 |
| 64 | b | 2 | 2 |
| 444 | c | 3 | 3 |
| 65 | d | 3 | 4 |
| 66 | m | 5 | 5 |
| 67 | s | 7 | 6 |
| 238 | x | 5 | 7 |
| 68 | z | 6 | 8 |
+-----------+-------+------------+------------+
You have just to transform the query to perform update instead of select.
answered Mar 12 '15 at 20:47
NicoNico
8721716
add a comment |
Try this :
SET @row_number = 0;
SELECT o.option_id,v.value,o.sort_order, (@row_number:=@row_number + 1) AS NewOrder
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND attribute_id=167
ORDER BY v.value;
The result will be:
+-----------+-------+------------+------------+
| option_id | value | sort_order | NewOrder |
+-----------+-------+------------+------------+
| 320 | a | 1 | 1 |
| 64 | b | 2 | 2 |
| 444 | c | 3 | 3 |
| 65 | d | 3 | 4 |
| 66 | m | 5 | 5 |
| 67 | s | 7 | 6 |
| 238 | x | 5 | 7 |
| 68 | z | 6 | 8 |
+-----------+-------+------------+------------+
You have just to transform the query to perform update instead of select.
answered Mar 12 '15 at 20:47
NicoNico
8721716
Try this :
SET @row_number = 0;
SELECT o.option_id,v.value,o.sort_order, (@row_number:=@row_number + 1) AS NewOrder
FROM eav_attribute_option o
INNER JOIN eav_attribute_option_value v
ON o.option_id=v.option_id
WHERE v.store_id=1
AND attribute_id=167
ORDER BY v.value;
The result will be:
+-----------+-------+------------+------------+
| option_id | value | sort_order | NewOrder |
+-----------+-------+------------+------------+
| 320 | a | 1 | 1 |
| 64 | b | 2 | 2 |
| 444 | c | 3 | 3 |
| 65 | d | 3 | 4 |
| 66 | m | 5 | 5 |
| 67 | s | 7 | 6 |
| 238 | x | 5 | 7 |
| 68 | z | 6 | 8 |
+-----------+-------+------------+------------+
You have just to transform the query to perform update instead of select.
answered Mar 12 '15 at 20:47
NicoNico
8721716
answered Mar 12 '15 at 20:47
NicoNico
8721716
answered Mar 12 '15 at 20:47
NicoNico
8721716
answered Mar 12 '15 at 20:47
NicoNico
8721716
8721716
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f95094%2ffilling-a-column-with-incremental-numbers%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
