Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

query to add incremental field based on GROUP BY

Tags:

sql

mysql

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
like image 818
cgmckeever Avatar asked Jun 11 '12 13:06

cgmckeever


People also ask

How do you add an incremental number field in SQL?

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .

Can we use AVG with GROUP BY in SQL?

The SQL AVG() Function With a GROUP BY ClauseThe SQL GROUP BY clause is used to group rows together. In most cases, a GROUP BY clause has one or more aggregate functions that calculate one or more metrics for the group.

How do you increment rows in SQL?

You can also make an auto increment in SQL to start from another value with the following syntax: ALTER TABLE table_name AUTO_INCREMENT = start_value; In the syntax above: start_value: It is the value from where you want to begin the numbering.

Can we use GROUP BY with where clause in MySQL?

The GROUP BY clause groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set. In this syntax, you place the GROUP BY clause after the FROM and WHERE clauses.


1 Answers

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

like image 136
GarethD Avatar answered Oct 05 '22 14:10

GarethD