query to add incremental field based on GROUP BY
Have a table photos
photos.id
photos.user_id
photos.order
A) Is it possible via a single query to group all photos by user and then update the order 1,2,3..N ?
B) added twist, what if some of the photos already have an order value associated? Make sure that the new photos.order never gets repeated and fills in ant orders lower or higher than those existing (as best as possible)
My only thought is just to run a script on this and loop through it and re'order' everything?
photos.id int(10)
photos.created_at datetime
photos.order int(10)
photos.user_id int(10)
Right now data may look like this
user_id = 1
photo_id = 1
order = NULL
user_id = 2
photo_id = 2
order = NULL
user_id = 1
photo_id = 3
order = NULL
the desired result would be
user_id = 1
photo_id = 1
order = 1
user_id = 2
photo_id = 2
order = 1
user_id = 1
photo_id = 3
order = 2
mysql sql
add a comment |
Have a table photos
photos.id
photos.user_id
photos.order
A) Is it possible via a single query to group all photos by user and then update the order 1,2,3..N ?
B) added twist, what if some of the photos already have an order value associated? Make sure that the new photos.order never gets repeated and fills in ant orders lower or higher than those existing (as best as possible)
My only thought is just to run a script on this and loop through it and re'order' everything?
photos.id int(10)
photos.created_at datetime
photos.order int(10)
photos.user_id int(10)
Right now data may look like this
user_id = 1
photo_id = 1
order = NULL
user_id = 2
photo_id = 2
order = NULL
user_id = 1
photo_id = 3
order = NULL
the desired result would be
user_id = 1
photo_id = 1
order = 1
user_id = 2
photo_id = 2
order = 1
user_id = 1
photo_id = 3
order = 2
mysql sql
add a comment |
Have a table photos
photos.id
photos.user_id
photos.order
A) Is it possible via a single query to group all photos by user and then update the order 1,2,3..N ?
B) added twist, what if some of the photos already have an order value associated? Make sure that the new photos.order never gets repeated and fills in ant orders lower or higher than those existing (as best as possible)
My only thought is just to run a script on this and loop through it and re'order' everything?
photos.id int(10)
photos.created_at datetime
photos.order int(10)
photos.user_id int(10)
Right now data may look like this
user_id = 1
photo_id = 1
order = NULL
user_id = 2
photo_id = 2
order = NULL
user_id = 1
photo_id = 3
order = NULL
the desired result would be
user_id = 1
photo_id = 1
order = 1
user_id = 2
photo_id = 2
order = 1
user_id = 1
photo_id = 3
order = 2
mysql sql
Have a table photos
photos.id
photos.user_id
photos.order
A) Is it possible via a single query to group all photos by user and then update the order 1,2,3..N ?
B) added twist, what if some of the photos already have an order value associated? Make sure that the new photos.order never gets repeated and fills in ant orders lower or higher than those existing (as best as possible)
My only thought is just to run a script on this and loop through it and re'order' everything?
photos.id int(10)
photos.created_at datetime
photos.order int(10)
photos.user_id int(10)
Right now data may look like this
user_id = 1
photo_id = 1
order = NULL
user_id = 2
photo_id = 2
order = NULL
user_id = 1
photo_id = 3
order = NULL
the desired result would be
user_id = 1
photo_id = 1
order = 1
user_id = 2
photo_id = 2
order = 1
user_id = 1
photo_id = 3
order = 2
mysql sql
mysql sql
asked Jun 11 '12 at 13:23
cgmckeevercgmckeever
6871715
6871715
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
A)
You can use a variable that increments with each row and resets with each user_ID to get the row count.
SELECT ID,
User_ID,
`Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `Order`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @r:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, ID
) AS Photos
Example on SQL Fiddle
B)
My First solution was to just add Order
to the sorting that adds the row number, therefore anything with an Order
Gets sorted by its order first, but this only works if your ordering system has no gaps and starts at 1:
SELECT ID,
User_ID,
RowNumber AS `Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `RowNumber`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @i:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, `Order`, ID
) AS Photos
ORDER BY `User_ID`, `Order`
Example using Order
Field
ORDERING WITH GAPS
I have eventually found a way of maintaining the sort order even when there are gaps in the sequence.
SELECT ID, User_ID, `Order`
FROM Photos
WHERE `Order` IS NOT NULL
UNION ALL
SELECT Photos.ID,
Photos.user_ID,
Numbers.RowNum
FROM ( SELECT ID,
User_ID,
@r1:= IF(@u1 = User_ID,@r1 + 1,1) AS RowNum,
@u1:= User_ID
FROM Photos,
(SELECT @r1:= 0) AS r,
(SELECT @u1:= 0) AS u
WHERE `Order` IS NULL
ORDER BY User_ID, ID
) AS Photos
INNER JOIN
( SELECT User_ID,
RowNum,
@r2:= IF(@u2 = User_ID,@r2 + 1,1) AS RowNum2,
@u2:= User_ID
FROM ( SELECT DISTINCT p.User_ID, o.RowNum
FROM Photos AS p,
( SELECT @i:= @i + 1 AS RowNum
FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
( SELECT @i:= 0) AS i
) AS o
WHERE RowNum <= (SELECT COUNT(*) FROM Photos P1 WHERE p.User_ID = p1.User_ID)
AND NOT EXISTS
( SELECT 1
FROM Photos p2
WHERE p.User_ID = p2.User_ID
AND o.RowNum = p2.`Order`
)
AND p.`Order` IS NULL
ORDER BY User_ID, RowNum
) AS p,
(SELECT @r2:= 0) AS r,
(SELECT @u2:= 0) AS u
ORDER BY user_ID, RowNum
) AS numbers
ON Photos.User_ID = numbers.User_ID
AND photos.RowNum = numbers.RowNum2
ORDER BY User_ID, `Order`
However as you can see this is pretty complicated. This works by treating those with an order
value separately to those without. The top query just ranks all photos with no order
value in order of ID for each user. The bottom query uses a cross join to generates a sequential list from 1 to n for each user ID (up to the number of entries for each User_ID). So with a data set like this:
ID User_ID Order
1 1 NULL
2 2 NULL
3 1 NULL
4 1 1
5 1 3
6 2 2
7 2 3
It would generate
UserID RowNum
1 1
1 2
1 3
1 4
2 1
2 2
2 3
It then uses NOT EXISTS
to elimiate all combinations already used by Photos with a non null order
, and ranked in order of RowNum partitioned by User_ID giving
UserID RowNum Rownum2
1 2 1
1 4 2
2 1 1
The RowNum2 value can then be matched with the rownum value achieved in the from subquery, giving the correct order
value. Long winded, but it works.
Example on SQL Fiddle
in-freaking-tense .. thanks for this ... I think for my sanity I may just script this out via code!!!
– cgmckeever
Jun 11 '12 at 16:26
Yeah, I'd have gone for that option too, but my stubborness meant once I'd started I had to find a solution!
– GarethD
Jun 11 '12 at 16:32
LOL -- yes -- I know what you mean .. I have done that on many many occasions .. but for this one, I think a straight up iteration loop that I can add some custom logic in to get things as close to parallel MUCH appreciated!
– cgmckeever
Jun 12 '12 at 14:51
add a comment |
Worked for me. I needed to increment version grouping by 4 fields (host, folder, fileName, status) and sort by 1 (downloadedAtTicks).
This is is my SELECT
SET @status := NULL;
SET @version := NULL;
SELECT
id,
host,
folder,
fileName,
status,
downloadedAtTicks,
version,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks;
And this is my UPDATE
SET @status := NULL;
SET @version := NULL;
UPDATE
csvsource csv,
(SELECT
id,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks) AS sub
SET
csv.version = sub.varVersion
WHERE csv.id = sub.id;
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%2f10980957%2fquery-to-add-incremental-field-based-on-group-by%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
A)
You can use a variable that increments with each row and resets with each user_ID to get the row count.
SELECT ID,
User_ID,
`Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `Order`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @r:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, ID
) AS Photos
Example on SQL Fiddle
B)
My First solution was to just add Order
to the sorting that adds the row number, therefore anything with an Order
Gets sorted by its order first, but this only works if your ordering system has no gaps and starts at 1:
SELECT ID,
User_ID,
RowNumber AS `Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `RowNumber`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @i:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, `Order`, ID
) AS Photos
ORDER BY `User_ID`, `Order`
Example using Order
Field
ORDERING WITH GAPS
I have eventually found a way of maintaining the sort order even when there are gaps in the sequence.
SELECT ID, User_ID, `Order`
FROM Photos
WHERE `Order` IS NOT NULL
UNION ALL
SELECT Photos.ID,
Photos.user_ID,
Numbers.RowNum
FROM ( SELECT ID,
User_ID,
@r1:= IF(@u1 = User_ID,@r1 + 1,1) AS RowNum,
@u1:= User_ID
FROM Photos,
(SELECT @r1:= 0) AS r,
(SELECT @u1:= 0) AS u
WHERE `Order` IS NULL
ORDER BY User_ID, ID
) AS Photos
INNER JOIN
( SELECT User_ID,
RowNum,
@r2:= IF(@u2 = User_ID,@r2 + 1,1) AS RowNum2,
@u2:= User_ID
FROM ( SELECT DISTINCT p.User_ID, o.RowNum
FROM Photos AS p,
( SELECT @i:= @i + 1 AS RowNum
FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
( SELECT @i:= 0) AS i
) AS o
WHERE RowNum <= (SELECT COUNT(*) FROM Photos P1 WHERE p.User_ID = p1.User_ID)
AND NOT EXISTS
( SELECT 1
FROM Photos p2
WHERE p.User_ID = p2.User_ID
AND o.RowNum = p2.`Order`
)
AND p.`Order` IS NULL
ORDER BY User_ID, RowNum
) AS p,
(SELECT @r2:= 0) AS r,
(SELECT @u2:= 0) AS u
ORDER BY user_ID, RowNum
) AS numbers
ON Photos.User_ID = numbers.User_ID
AND photos.RowNum = numbers.RowNum2
ORDER BY User_ID, `Order`
However as you can see this is pretty complicated. This works by treating those with an order
value separately to those without. The top query just ranks all photos with no order
value in order of ID for each user. The bottom query uses a cross join to generates a sequential list from 1 to n for each user ID (up to the number of entries for each User_ID). So with a data set like this:
ID User_ID Order
1 1 NULL
2 2 NULL
3 1 NULL
4 1 1
5 1 3
6 2 2
7 2 3
It would generate
UserID RowNum
1 1
1 2
1 3
1 4
2 1
2 2
2 3
It then uses NOT EXISTS
to elimiate all combinations already used by Photos with a non null order
, and ranked in order of RowNum partitioned by User_ID giving
UserID RowNum Rownum2
1 2 1
1 4 2
2 1 1
The RowNum2 value can then be matched with the rownum value achieved in the from subquery, giving the correct order
value. Long winded, but it works.
Example on SQL Fiddle
in-freaking-tense .. thanks for this ... I think for my sanity I may just script this out via code!!!
– cgmckeever
Jun 11 '12 at 16:26
Yeah, I'd have gone for that option too, but my stubborness meant once I'd started I had to find a solution!
– GarethD
Jun 11 '12 at 16:32
LOL -- yes -- I know what you mean .. I have done that on many many occasions .. but for this one, I think a straight up iteration loop that I can add some custom logic in to get things as close to parallel MUCH appreciated!
– cgmckeever
Jun 12 '12 at 14:51
add a comment |
A)
You can use a variable that increments with each row and resets with each user_ID to get the row count.
SELECT ID,
User_ID,
`Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `Order`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @r:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, ID
) AS Photos
Example on SQL Fiddle
B)
My First solution was to just add Order
to the sorting that adds the row number, therefore anything with an Order
Gets sorted by its order first, but this only works if your ordering system has no gaps and starts at 1:
SELECT ID,
User_ID,
RowNumber AS `Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `RowNumber`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @i:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, `Order`, ID
) AS Photos
ORDER BY `User_ID`, `Order`
Example using Order
Field
ORDERING WITH GAPS
I have eventually found a way of maintaining the sort order even when there are gaps in the sequence.
SELECT ID, User_ID, `Order`
FROM Photos
WHERE `Order` IS NOT NULL
UNION ALL
SELECT Photos.ID,
Photos.user_ID,
Numbers.RowNum
FROM ( SELECT ID,
User_ID,
@r1:= IF(@u1 = User_ID,@r1 + 1,1) AS RowNum,
@u1:= User_ID
FROM Photos,
(SELECT @r1:= 0) AS r,
(SELECT @u1:= 0) AS u
WHERE `Order` IS NULL
ORDER BY User_ID, ID
) AS Photos
INNER JOIN
( SELECT User_ID,
RowNum,
@r2:= IF(@u2 = User_ID,@r2 + 1,1) AS RowNum2,
@u2:= User_ID
FROM ( SELECT DISTINCT p.User_ID, o.RowNum
FROM Photos AS p,
( SELECT @i:= @i + 1 AS RowNum
FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
( SELECT @i:= 0) AS i
) AS o
WHERE RowNum <= (SELECT COUNT(*) FROM Photos P1 WHERE p.User_ID = p1.User_ID)
AND NOT EXISTS
( SELECT 1
FROM Photos p2
WHERE p.User_ID = p2.User_ID
AND o.RowNum = p2.`Order`
)
AND p.`Order` IS NULL
ORDER BY User_ID, RowNum
) AS p,
(SELECT @r2:= 0) AS r,
(SELECT @u2:= 0) AS u
ORDER BY user_ID, RowNum
) AS numbers
ON Photos.User_ID = numbers.User_ID
AND photos.RowNum = numbers.RowNum2
ORDER BY User_ID, `Order`
However as you can see this is pretty complicated. This works by treating those with an order
value separately to those without. The top query just ranks all photos with no order
value in order of ID for each user. The bottom query uses a cross join to generates a sequential list from 1 to n for each user ID (up to the number of entries for each User_ID). So with a data set like this:
ID User_ID Order
1 1 NULL
2 2 NULL
3 1 NULL
4 1 1
5 1 3
6 2 2
7 2 3
It would generate
UserID RowNum
1 1
1 2
1 3
1 4
2 1
2 2
2 3
It then uses NOT EXISTS
to elimiate all combinations already used by Photos with a non null order
, and ranked in order of RowNum partitioned by User_ID giving
UserID RowNum Rownum2
1 2 1
1 4 2
2 1 1
The RowNum2 value can then be matched with the rownum value achieved in the from subquery, giving the correct order
value. Long winded, but it works.
Example on SQL Fiddle
in-freaking-tense .. thanks for this ... I think for my sanity I may just script this out via code!!!
– cgmckeever
Jun 11 '12 at 16:26
Yeah, I'd have gone for that option too, but my stubborness meant once I'd started I had to find a solution!
– GarethD
Jun 11 '12 at 16:32
LOL -- yes -- I know what you mean .. I have done that on many many occasions .. but for this one, I think a straight up iteration loop that I can add some custom logic in to get things as close to parallel MUCH appreciated!
– cgmckeever
Jun 12 '12 at 14:51
add a comment |
A)
You can use a variable that increments with each row and resets with each user_ID to get the row count.
SELECT ID,
User_ID,
`Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `Order`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @r:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, ID
) AS Photos
Example on SQL Fiddle
B)
My First solution was to just add Order
to the sorting that adds the row number, therefore anything with an Order
Gets sorted by its order first, but this only works if your ordering system has no gaps and starts at 1:
SELECT ID,
User_ID,
RowNumber AS `Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `RowNumber`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @i:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, `Order`, ID
) AS Photos
ORDER BY `User_ID`, `Order`
Example using Order
Field
ORDERING WITH GAPS
I have eventually found a way of maintaining the sort order even when there are gaps in the sequence.
SELECT ID, User_ID, `Order`
FROM Photos
WHERE `Order` IS NOT NULL
UNION ALL
SELECT Photos.ID,
Photos.user_ID,
Numbers.RowNum
FROM ( SELECT ID,
User_ID,
@r1:= IF(@u1 = User_ID,@r1 + 1,1) AS RowNum,
@u1:= User_ID
FROM Photos,
(SELECT @r1:= 0) AS r,
(SELECT @u1:= 0) AS u
WHERE `Order` IS NULL
ORDER BY User_ID, ID
) AS Photos
INNER JOIN
( SELECT User_ID,
RowNum,
@r2:= IF(@u2 = User_ID,@r2 + 1,1) AS RowNum2,
@u2:= User_ID
FROM ( SELECT DISTINCT p.User_ID, o.RowNum
FROM Photos AS p,
( SELECT @i:= @i + 1 AS RowNum
FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
( SELECT @i:= 0) AS i
) AS o
WHERE RowNum <= (SELECT COUNT(*) FROM Photos P1 WHERE p.User_ID = p1.User_ID)
AND NOT EXISTS
( SELECT 1
FROM Photos p2
WHERE p.User_ID = p2.User_ID
AND o.RowNum = p2.`Order`
)
AND p.`Order` IS NULL
ORDER BY User_ID, RowNum
) AS p,
(SELECT @r2:= 0) AS r,
(SELECT @u2:= 0) AS u
ORDER BY user_ID, RowNum
) AS numbers
ON Photos.User_ID = numbers.User_ID
AND photos.RowNum = numbers.RowNum2
ORDER BY User_ID, `Order`
However as you can see this is pretty complicated. This works by treating those with an order
value separately to those without. The top query just ranks all photos with no order
value in order of ID for each user. The bottom query uses a cross join to generates a sequential list from 1 to n for each user ID (up to the number of entries for each User_ID). So with a data set like this:
ID User_ID Order
1 1 NULL
2 2 NULL
3 1 NULL
4 1 1
5 1 3
6 2 2
7 2 3
It would generate
UserID RowNum
1 1
1 2
1 3
1 4
2 1
2 2
2 3
It then uses NOT EXISTS
to elimiate all combinations already used by Photos with a non null order
, and ranked in order of RowNum partitioned by User_ID giving
UserID RowNum Rownum2
1 2 1
1 4 2
2 1 1
The RowNum2 value can then be matched with the rownum value achieved in the from subquery, giving the correct order
value. Long winded, but it works.
Example on SQL Fiddle
A)
You can use a variable that increments with each row and resets with each user_ID to get the row count.
SELECT ID,
User_ID,
`Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `Order`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @r:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, ID
) AS Photos
Example on SQL Fiddle
B)
My First solution was to just add Order
to the sorting that adds the row number, therefore anything with an Order
Gets sorted by its order first, but this only works if your ordering system has no gaps and starts at 1:
SELECT ID,
User_ID,
RowNumber AS `Order`
FROM ( SELECT @r:= IF(@u = User_ID, @r + 1,1) AS `RowNumber`,
ID,
User_ID,
@u:= User_ID
FROM Photos,
(SELECT @i:= 1) AS r,
(SELECT @u:= 0) AS u
ORDER BY User_ID, `Order`, ID
) AS Photos
ORDER BY `User_ID`, `Order`
Example using Order
Field
ORDERING WITH GAPS
I have eventually found a way of maintaining the sort order even when there are gaps in the sequence.
SELECT ID, User_ID, `Order`
FROM Photos
WHERE `Order` IS NOT NULL
UNION ALL
SELECT Photos.ID,
Photos.user_ID,
Numbers.RowNum
FROM ( SELECT ID,
User_ID,
@r1:= IF(@u1 = User_ID,@r1 + 1,1) AS RowNum,
@u1:= User_ID
FROM Photos,
(SELECT @r1:= 0) AS r,
(SELECT @u1:= 0) AS u
WHERE `Order` IS NULL
ORDER BY User_ID, ID
) AS Photos
INNER JOIN
( SELECT User_ID,
RowNum,
@r2:= IF(@u2 = User_ID,@r2 + 1,1) AS RowNum2,
@u2:= User_ID
FROM ( SELECT DISTINCT p.User_ID, o.RowNum
FROM Photos AS p,
( SELECT @i:= @i + 1 AS RowNum
FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
( SELECT @i:= 0) AS i
) AS o
WHERE RowNum <= (SELECT COUNT(*) FROM Photos P1 WHERE p.User_ID = p1.User_ID)
AND NOT EXISTS
( SELECT 1
FROM Photos p2
WHERE p.User_ID = p2.User_ID
AND o.RowNum = p2.`Order`
)
AND p.`Order` IS NULL
ORDER BY User_ID, RowNum
) AS p,
(SELECT @r2:= 0) AS r,
(SELECT @u2:= 0) AS u
ORDER BY user_ID, RowNum
) AS numbers
ON Photos.User_ID = numbers.User_ID
AND photos.RowNum = numbers.RowNum2
ORDER BY User_ID, `Order`
However as you can see this is pretty complicated. This works by treating those with an order
value separately to those without. The top query just ranks all photos with no order
value in order of ID for each user. The bottom query uses a cross join to generates a sequential list from 1 to n for each user ID (up to the number of entries for each User_ID). So with a data set like this:
ID User_ID Order
1 1 NULL
2 2 NULL
3 1 NULL
4 1 1
5 1 3
6 2 2
7 2 3
It would generate
UserID RowNum
1 1
1 2
1 3
1 4
2 1
2 2
2 3
It then uses NOT EXISTS
to elimiate all combinations already used by Photos with a non null order
, and ranked in order of RowNum partitioned by User_ID giving
UserID RowNum Rownum2
1 2 1
1 4 2
2 1 1
The RowNum2 value can then be matched with the rownum value achieved in the from subquery, giving the correct order
value. Long winded, but it works.
Example on SQL Fiddle
edited Mar 12 '15 at 16:17
Alex
4,60164685
4,60164685
answered Jun 11 '12 at 16:18
GarethDGarethD
53.7k66393
53.7k66393
in-freaking-tense .. thanks for this ... I think for my sanity I may just script this out via code!!!
– cgmckeever
Jun 11 '12 at 16:26
Yeah, I'd have gone for that option too, but my stubborness meant once I'd started I had to find a solution!
– GarethD
Jun 11 '12 at 16:32
LOL -- yes -- I know what you mean .. I have done that on many many occasions .. but for this one, I think a straight up iteration loop that I can add some custom logic in to get things as close to parallel MUCH appreciated!
– cgmckeever
Jun 12 '12 at 14:51
add a comment |
in-freaking-tense .. thanks for this ... I think for my sanity I may just script this out via code!!!
– cgmckeever
Jun 11 '12 at 16:26
Yeah, I'd have gone for that option too, but my stubborness meant once I'd started I had to find a solution!
– GarethD
Jun 11 '12 at 16:32
LOL -- yes -- I know what you mean .. I have done that on many many occasions .. but for this one, I think a straight up iteration loop that I can add some custom logic in to get things as close to parallel MUCH appreciated!
– cgmckeever
Jun 12 '12 at 14:51
in-freaking-tense .. thanks for this ... I think for my sanity I may just script this out via code!!!
– cgmckeever
Jun 11 '12 at 16:26
in-freaking-tense .. thanks for this ... I think for my sanity I may just script this out via code!!!
– cgmckeever
Jun 11 '12 at 16:26
Yeah, I'd have gone for that option too, but my stubborness meant once I'd started I had to find a solution!
– GarethD
Jun 11 '12 at 16:32
Yeah, I'd have gone for that option too, but my stubborness meant once I'd started I had to find a solution!
– GarethD
Jun 11 '12 at 16:32
LOL -- yes -- I know what you mean .. I have done that on many many occasions .. but for this one, I think a straight up iteration loop that I can add some custom logic in to get things as close to parallel MUCH appreciated!
– cgmckeever
Jun 12 '12 at 14:51
LOL -- yes -- I know what you mean .. I have done that on many many occasions .. but for this one, I think a straight up iteration loop that I can add some custom logic in to get things as close to parallel MUCH appreciated!
– cgmckeever
Jun 12 '12 at 14:51
add a comment |
Worked for me. I needed to increment version grouping by 4 fields (host, folder, fileName, status) and sort by 1 (downloadedAtTicks).
This is is my SELECT
SET @status := NULL;
SET @version := NULL;
SELECT
id,
host,
folder,
fileName,
status,
downloadedAtTicks,
version,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks;
And this is my UPDATE
SET @status := NULL;
SET @version := NULL;
UPDATE
csvsource csv,
(SELECT
id,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks) AS sub
SET
csv.version = sub.varVersion
WHERE csv.id = sub.id;
add a comment |
Worked for me. I needed to increment version grouping by 4 fields (host, folder, fileName, status) and sort by 1 (downloadedAtTicks).
This is is my SELECT
SET @status := NULL;
SET @version := NULL;
SELECT
id,
host,
folder,
fileName,
status,
downloadedAtTicks,
version,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks;
And this is my UPDATE
SET @status := NULL;
SET @version := NULL;
UPDATE
csvsource csv,
(SELECT
id,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks) AS sub
SET
csv.version = sub.varVersion
WHERE csv.id = sub.id;
add a comment |
Worked for me. I needed to increment version grouping by 4 fields (host, folder, fileName, status) and sort by 1 (downloadedAtTicks).
This is is my SELECT
SET @status := NULL;
SET @version := NULL;
SELECT
id,
host,
folder,
fileName,
status,
downloadedAtTicks,
version,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks;
And this is my UPDATE
SET @status := NULL;
SET @version := NULL;
UPDATE
csvsource csv,
(SELECT
id,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks) AS sub
SET
csv.version = sub.varVersion
WHERE csv.id = sub.id;
Worked for me. I needed to increment version grouping by 4 fields (host, folder, fileName, status) and sort by 1 (downloadedAtTicks).
This is is my SELECT
SET @status := NULL;
SET @version := NULL;
SELECT
id,
host,
folder,
fileName,
status,
downloadedAtTicks,
version,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks;
And this is my UPDATE
SET @status := NULL;
SET @version := NULL;
UPDATE
csvsource csv,
(SELECT
id,
IF(IF(status IS NULL, 0, status) = @status, @version := @version + 1, @version := 0) AS varVersion,
@status := IF(status IS NULL, 0, status) AS varStatus
FROM csvsource
ORDER BY host, folder, fileName, status, downloadedAtTicks) AS sub
SET
csv.version = sub.varVersion
WHERE csv.id = sub.id;
answered Nov 28 '18 at 15:25
Eduard StreltsovEduard Streltsov
15128
15128
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.
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%2f10980957%2fquery-to-add-incremental-field-based-on-group-by%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