SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    Row count as column

    I was wondering if anyone had run into the following situation:

    Code:
    table_id | foreign_key1 | foreign_key2
    I have rows that look like this:

    Code:
    1 | 1 | 2
    1 | 1 | 3
    ..
    The foreign_key2 is the unique value. What I would like to do is have a sequential count of where table_id and foreign_key1 are the same.

    So for the above I would get something like:

    Code:
    1 | 1 | 2 | 1
    1 | 1 | 3 | 2
    In Oracle I could call the ROW_NUMBER() function. Is there anything similar to that in MySQL? My searches have only brought back stored procedures that won't work for my situation.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Hartmann View Post
    What I would like to do is have a sequential count of where table_id and foreign_key1 are the same.
    when you say "have" does this means you want to generate the count in the output of a query, or store the count in a 4th column in the table?

    if you mean the former, don't do it with sql -- do the count in whatever application language you're using to display the result set

    if you mean the latter, don't do it at all -- never store something that can be obtained so easily with an extraction (and that can potentially change)

    that said, if you want to store the sequence number, you actually can do that with mysql auto_increments

    helps?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    when you say "have" does this means you want to generate the count in the output of a query, or store the count in a 4th column in the table?

    if you mean the former, don't do it with sql -- do the count in whatever application language you're using to display the result set

    if you mean the latter, don't do it at all -- never store something that can be obtained so easily with an extraction (and that can potentially change)

    that said, if you want to store the sequence number, you actually can do that with mysql auto_increments

    helps?
    I mean the former. It can't be an auto_increment because the count would be reset after each set of groupings.

    Code:
    1 | 1 | 2 | 1
    1 | 1 | 3 | 2
    2 | 2 | 6 | 1
    2 | 2 | 7 | 2
    2 | 2 | 8 | 3

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it ~can~ be an auto_increment, and reset after each grouping, if that's what you want, but they will be permanent, and renumbering them will be difficult

    but if what you really want is just to number them in a query, then you should do that in the application code
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it ~can~ be an auto_increment, and reset after each grouping, if that's what you want, but they will be permanent, and renumbering them will be difficult

    but if what you really want is just to number them in a query, then you should do that in the application code
    I think they can be permanent, but will need to double check. I didn't think that MySQL's auto_increment function could be used across three columns.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Hartmann View Post
    I didn't think that MySQL's auto_increment function could be used across three columns.
    to be honest, i haven't tested it on three, just on two, like in da manual

    http://dev.mysql.com/doc/refman/5.0/...increment.html

    myisam and bdb tables only, though
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •