SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding the row number

    Hi,

    I should know this really, but never mind.

    I've got a select statement e.g:

    Code:
    SELECT column1 FROM table ORDER BY column1 desc
    I need to find out what row number a value in column1 is in.

    If the data returned was this:

    value1
    value2
    value3
    value4
    value5
    value6

    I would need to know that value4 is on row 3 (as MySQL counts from 0).

    I could do some PHP loops although I'm sure that there is a simple MySQL statement to do what I need.

    Any idea's?


    Thanks,

    Rob

  2. #2
    I have an opinion...
    Join Date
    Sep 2001
    Location
    Barrie, Ontario
    Posts
    324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     SELECT row_id_column, column1 FROM tbl_name ORDER BY column1 DESC
    Then, your data will be returned as:

    Code:
    row_number  value
    row_number  value
    row_number  value
    And you can still display only the column1 data, but you'll have the row number.
    Egotist: A person more interested in himself than in me.
    KodeKrash - Eidix - Barrie LUG

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    There isn't a row id column though, I'm looking to get MySQL to count what row it is on. Or is row_id_column a pre-made variable what MySQL uses to number each row?

    Thanks,

    Rob


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
  •