SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Apr 2009
    Location
    Acapulco, Mexico
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Adding an incremental column ( How to )

    Hi guys,

    I need to add one column into the recordset having an incremented number depending upon the records matching the query.

    SELECT f1,f2,f3,?????
    FROM mytbl
    WHERE f1=1


    1 joe male 1
    1 john male 2
    1 peter male 3
    1 brian male 4


    I found this example :

    SELECT @m:=0;
    SELECT sum(Sales) AS TotSales, SalesManager, @m:=@m+1 AS Rank

    it works fine when I use Navicat or SqlYog, but I get errors when I try to run it from the app.

    Thanks in advance.

  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)
    if you're running it from an application language, then do yourself a humoungous favour, assign the number with your app while you're looping over the results returned by the query

    it's rather expensive (in processing cycles) to do this with SQL
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2009
    Location
    Acapulco, Mexico
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Thanks a lot for this quick reply.

    Ok, I'll follow your advice and I'll do it by code.

    Thanks again


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
  •