Filling a column with incremental numbersINNER JOIN between 2 tables vs WHERE on one table performanceHow to...
How can I get my players to come to the game session after agreeing to a date?
Absorbing damage with Planeswalker
Why don't hotels mount air conditioning units outside the rooms?
Should I reinstall Linux when changing the laptop's CPU?
Do authors have to be politically correct in article-writing?
How to read 火日参拾月参
Play Zip, Zap, Zop
What are "industrial chops"?
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?
What would the chemical name be for C13H8Cl3NO
A Missing Symbol for This Logo
What are the exceptions to Natural Selection?
Is using an 'empty' metaphor considered bad style?
How to make ice magic work from a scientific point of view?
Why is it that Bernie Sanders is always called a "socialist"?
Citing paywalled articles accessed via illegal web sharing
Early credit roll before the end of the film
If I delete my router's history can my ISP still provide it to my parents?
Do theoretical physics suggest that gravity is the exchange of gravitons or deformation/bending of spacetime?
How do you funnel food off a cutting board?
A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?
Why am I able to open Wireshark and capture packets in macOS without root privileges?
What is the purpose of easy combat scenarios that don't need resource expenditure?
How do I append a character to the end of every line in an Excel cell?
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♦ 10 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♦ 10 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♦ 10 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♦ 10 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
