Last record

**viki1967** · Feb 9, 2010 05:00

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

**r937** · Feb 9, 2010 05:13

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

**viki1967** · Feb 9, 2010 08:56

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

**r937** · Feb 9, 2010 09:18

you forgot to put the count into the outer query's SELECT clause

**viki1967** · Feb 9, 2010 09:21

Originally Posted by r937

you forgot to put the count into the outer query's SELECT clause

Sorry, not understand...

**r937** · Feb 9, 2010 09:36

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

**viki1967** · Feb 9, 2010 10:39

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'

**r937** · Feb 9, 2010 10:59

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?

**viki1967** · Feb 9, 2010 11:12

I need count the records of daTable as provided in your query...

**r937** · Feb 9, 2010 11:24

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

**viki1967** · Feb 9, 2010 11:56

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?

**r937** · Feb 9, 2010 12:01

Originally Posted by viki1967

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

**viki1967** · Feb 9, 2010 12:05

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...

**r937** · Feb 9, 2010 12:15

now you are just guessing

please go back to post #8, and read it carefully

**viki1967** · Feb 9, 2010 13:14

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

**r937** · Feb 9, 2010 13:58

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

**viki1967** · Feb 10, 2010 02:38

Originally Posted by r937

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

**guido2004** · Feb 10, 2010 02:49

Originally Posted by viki1967

[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.

**r937** · Feb 10, 2010 04:54

viki, the query you posted in post #17 is ~not~ the query i asked you to run

please see post #16

**viki1967** · Feb 10, 2010 07:41

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!

User Tag List

Thread: Last record

Thread Tools

Display

Last record

Bookmarks

Bookmarks

Posting Permissions