SitePoint Sponsor

User Tag List

Results 1 to 20 of 20

Thread: Last record

  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Last record

    Hi all.

    I have this table in db mysql:

    Code:
    ID	CODE		UploadDT		CP		NAME		DATE
    2550	DH00045226	2005-11-30 13:44:46	LUGAGNANO	TRS. 01		2005-08-23
    3290	DH00045226	2006-05-04 11:38:40	LUGAGNANO	TRS. 01		2006-03-28
    3787	DH00045226	2006-11-03 09:31:58	LUGAGNANO	TRS. 01		2006-07-31
    3805	DH00045226	2006-11-03 10:41:58	LUGAGNANO	TRS. 01		2006-09-22
    I need this output:

    Code:
    ID	CODE		UploadDT		CP		NAME		DATE
    3805	DH00045226	2006-11-03 10:41:58	LUGAGNANO	TRS. 01		2006-09-22
    I see only last record of CODE.
    Can you help me?

    Regards
    Viki

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
      FROM ( SELECT code
                  , MAX(date) AS max_date
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for your help.

    Now I need count recordset in the table.
    I try this but I have error, can you help me?

    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
      FROM ( SELECT code
                  , MAX(date) AS max_date
                  , COUNT(date) AS cnt
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you forgot to put the count into the outer query's SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you forgot to put the count into the outer query's SELECT clause
    Sorry, not understand...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this is the outer query --
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
      FROM ( ... ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date
    this is the subquery --
    Code:
    SELECT code
         , MAX(date) AS max_date
         , COUNT(date) AS cnt
      FROM daTable
    GROUP
        BY code
    a subquery in the FROM clause of the outer query is called a derived table and you can think of it exactly like any other table

    so in the outer query, if you want to show a column from the derived table, you must put that column into the SELECT clause

    this is the SELECT clause of the outer table --
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
    see? the count is missing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
         , t.CNT
      FROM ( ... ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date
    Response with:

    [MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]Unknown column 't.CNT' in 'field list'


  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what do you think that this error message is trying to tell you?

    there is no column called "cnt" in the "t" table

    since there are only two tables in your query, which one do you think has the "cnt" column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need count the records of daTable as provided in your query...

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    total overall count? just use a separate query

    or count per code? if this, then you simply must take the time to understand what i have been trying to teach you, rather than just demanding an answer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
         , COUNT(date) AS cnt
      FROM ( SELECT code
                  , MAX(date) AS max_date
                  , COUNT(date) AS cnt
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date
    MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

    Can you help me?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    Can you help me?
    no

    i have given you sufficient information right in this thread for you to figure out the solution

    i suggest that you go over all the posts in this thread carefully, and i am sure you will find the answer yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
         , COUNT(date) AS cnt
      FROM ( SELECT code
                  , MAX(date) AS max_date
                  , COUNT(date) AS cnt
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date
             GROUP
                 BY code
    Not error but I don't see all records...

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    now you are just guessing

    please go back to post #8, and read it carefully
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for your help.
    I write two queries and working.

    Query 1:
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
      FROM ( SELECT code
                  , MAX(date) AS max_date
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date
    Query 2:
    Code:
    SELECT
         COUNT(date) AS cnt
      FROM ( SELECT code
                  , MAX(date) AS max_date
                  , COUNT(date) AS cnt
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    change your second query to this --
    Code:
    SELECT COUNT(DISTINCT code) FROM daTable
    alternatively, if you are using an application language like php, you don't even need a second query, just use mysql_num_rows (or the equivalent) after the first query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    change your second query to this --
    Code:
    SELECT COUNT(DISTINCT code) FROM daTable
    alternatively, if you are using an application language like php, you don't even need a second query, just use mysql_num_rows (or the equivalent) after the first query
    thanks, but:

    Query 2:

    Code:
    SELECT
         COUNT(DISTINCT code)
      FROM ( SELECT code
                  , MAX(date) AS max_date
                  , COUNT(DISTINCT code)
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date

    [MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]Column 'code' in field list is ambiguous

  18. #18
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by viki1967 View Post
    [MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]Column 'code' in field list is ambiguous
    Well, that message is quite clear, isn't it? The column name 'code' is ambiguous, because it exists in both tables. You'll have to specify which of the two 'code' columns you want to count: t.code or m.code.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    viki, the query you posted in post #17 is ~not~ the query i asked you to run

    please see post #16
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'am sorry I dont understand your suggestion... now working

    Query 1:
    Code:
    SELECT t.id
         , t.code
         , t.uploaddt
         , t.cp
         , t.name
         , t.date
      FROM ( SELECT code
                  , MAX(date) AS max_date
               FROM daTable
             GROUP
                 BY code ) AS m
    INNER
      JOIN daTable AS t
        ON t.code = m.code
       AND t.date = m.max_date
    Query 2:
    Code:
    SELECT
         COUNT(DISTINCT code)        
            FROM daTable
    Many thanks!


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
  •