SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Location
    bangalore
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help me to write this query please

    Dear all
    iam new for asp


    I have data in database as like as follows(database is MS ACESS)

    Name company shares date
    A x 100 1/1/07
    A x 200 5/1/07
    A y 100 2/1/07
    A y 500 5/1/07
    I need to display as follows

    Name company shares date
    A x 200 5/1/07
    A y 500 5/1/07

    U can notice the latest updating for each name for all companies
    This is the output I should show in my web page
    its urgent to finish my project

    regards
    sona

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select Name 
         , company 
         , shares 
         , [date]
      from daTable as T
     where [date] = 
           ( select max([date])
               from daTable
              where company = T.company )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Location
    bangalore
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    correct my error please

    iam getting the error as follows
    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error in FROM clause

    following is the showing error

    my_sql="SELECT company_name,[tran_date] FROM data as T"&"WHERE [tran_date]=(select max[tran_date] from data & where company_name=T.company_name)"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Location
    bangalore
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    please anybody correct this error

    iam getting the error as follows
    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error in FROM clause

    following is the showing error

    my_sql="SELECT company_name,[tran_date] FROM data as T"&"WHERE [tran_date]=(select max[tran_date] from data & where company_name=T.company_name)"

  5. #5
    SitePoint Guru SSJ's Avatar
    Join Date
    Jan 2007
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You Can Use the Following Query:

    my_sql = "select company_name,[tran_date] from data where [tran_date] In (Select Max([tran_date]) from data)"

    -SSJ

  6. #6
    SitePoint Guru SSJ's Avatar
    Join Date
    Jan 2007
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You Can Use the Following Query:

    my_sql = "select company_name,[tran_date] from data where [tran_date] In (Select Max([tran_date]) from data)"

    -SSJ

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    SSJ, that's not the same query, yours just gets one row equal to the latest transaction overall, whereas what is desired is the latest transaction for each company

    sona, it looks like your problem is the ampersand in front of the second WHERE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Location
    bangalore
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i tried the following query
    it is taking max date(today) from database and it displaying all companies which are having the date as today's date value

    my_sql="SELECT company_name,ticker,no_of_shares,ownership_percentage,[tran_date] FROM data as T " & " WHERE [tran_date]=(select max([tran_date]) from data where company_name=T.company_name) and investor_name='"&request.querystring("investor_name")&"'"

    i need to display all details(company,shares,date etc..) of an investor
    but the thing is for a particular investor
    all companies and other details haveto displayed with latest updated data of each companies for that investor

    problem in my query only it is retriving one date(max) from database it is not considering each companies

    please correct my query

    thanks in advance


    but i need to display


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
  •