#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    95
    Rep Power
    3

    Grouped autoincrement in select


    Hi,

    I'm not sure if this is possible with just MySQL.
    I'm trying to return results from a query so that they are numbered by the results grouped by a particular variable or combination of variables.

    Here's my current query:
    PHP Code:
    SELECT  names.nameaPattern.patterngroups.grpname FROM aPattern 
    left join names on aPattern
    .nameid=names.id 
    left join groups on aPattern
    .grpid=groups.grpid
    WHERE  aPattern
    .isactive=and .....................
    order by aPattern.patternaPattern.grpidaPattern.priority desc 
    the aPattern.priority is the priority within each pattern, but it's descending and includes the inactive patterns.

    what i'm looking for is a numbered ascending list (like an autoincrement) for each pattern and group such as

    num | Name | Pattern | Group
    1 | Bob | 123 | A
    2 | Joe | 123 | A
    1 | Bob | 123 | B
    2 | Jim | 123 | B
    1 | Joe | 124 | A
    1 | Jim | 124 | B

    any ideas?
    Last edited by bobert123; May 23rd, 2013 at 11:58 AM.
  2. #2
  3. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    While possible to do in MySQL it's (I would say) easier to do in code. Is that an option?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    95
    Rep Power
    3
    Originally Posted by requinix
    While possible to do in MySQL it's (I would say) easier to do in code. Is that an option?
    unfortunately, not at the moment.
  6. #4
  7. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    Disclaimer: MySQL specifically warns against what I'm about to do. You can't rely on it to work forever as it depends on the behavior of the server and how it executes the query.

    User-defined variables. You need to execute two statements, with the added one to initialize variables.
    Code:
    SET @num = 1, @pattern = 0, @grpname = "";
    
    SELECT
    	@num := IF(@pattern = aPattern.pattern AND @grpname = groups.grpname, @num + 1, 1) AS num,
    	names.name AS Name,
    	@pattern := aPattern.pattern AS Pattern,
    	@grpname := groups.grpname AS `Group`
    FROM aPattern
    LEFT JOIN ...
    WHERE ...
    ORDER BY aPattern.pattern, groups.grpname, ...;
    For each row,
    1. @num is set to either @num + 1 if the pattern and group match the previous (stored in @pattern and @grpname respectively), otherwise 1. This value is returned.
    2. @pattern and @grpname are both set. Those values are also returned.
    Notice that I changed the ORDER BY to go with the group name and not its id, as the ranking number varies with the name.

    ---

    I think there's another more reliable method but I don't remember what it is. A third (or second) method is a temporary table that looks like
    Code:
    CREATE TEMPORARY TABLE example (
    	num INT UNSIGNED NOT NULL AUTO_INCREMENT,
    	Name...,
    	Pattern...,
    	`Group`...,
    	PRIMARY KEY (Pattern, `Group`, num)
    )
    with
    Code:
    INSERT INTO example
    SELECT NULL, names.name, aPattern.pattern, groups.grpname
    FROM aPattern
    ...
    and to use it
    Code:
    SELECT * FROM example ORDER BY Pattern, `Group`, num

    Comments on this post

    • bobert123 agrees
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    95
    Rep Power
    3
    wow! thanks.

IMN logo majestic logo threadwatch logo seochat tools logo