Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Location
    India
    Posts
    13

    Unanswered: Row_number function in mysql

    Hi everybody,

    I've googled this question but didn't find anything relevant.

    How can I create this query in MySQL?

    select *, row_number() over (partition by GroupID order by DateField DESC) as RowNum from myTable

    ?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pasthana@netlink. View Post
    How can I create this query in MySQL?
    while it is possible to generate those numbers (using a self-join) it is very inefficient

    it's far easier to apply the numbers in your application language (php or whatever) as you loop over the query results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    14
    I've never needed to do this myself, I came up with this just now - as such use with caution, as there may be limitations. But this may at least get you on the right path:

    Code:
    select
      @i := if(@lastGroupID != GroupID, 1, $i + 1) as row_number,
      @lastGroupID := GroupID,
      myTable.*
    
    from
      myTable,
      (select @i := 0) row_number_table,
      (select @lastGroupID := null) group_id_table
    
    order by GroupID
    ;
    Basically @i is a row number increment variable and @lastGroupID stores the GroupID. For each row that returns, @i is either incremented or reset if the new GroupID is different from the old one (stored in @lastGroupID from the last run). After @i is sorted out, @lastGroupID is assigned the new GroupID for the next run. It's the same logic you would use in your application code, just making the database server do the heavy lifting (the way SQL Server does).
    Last edited by BrianSteffens; 07-18-11 at 04:31. Reason: renamed @l to @lastGroupID.. @i, @l, and 1 made it hard to read :)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    brian, that's pretty impressive

    i keep forgetting mysql allows variables

    nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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