Last record

system · February 9, 2010, 11:00am

Hi all.

I have this table in db mysql:

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:

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 · February 9, 2010, 11:13am

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

system · February 9, 2010, 2:56pm

Many thanks for your help.

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

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 · February 9, 2010, 3:18pm

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

system · February 9, 2010, 3:21pm

Sorry, not understand…

r937 · February 9, 2010, 3:36pm

this is the outer query –

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 –

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 –

SELECT t.id
     , t.code
     , t.uploaddt
     , t.cp
     , t.name
     , t.date

see? the count is missing

system · February 9, 2010, 4:39pm

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’

:sick:

r937 · February 9, 2010, 4:59pm

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?

system · February 9, 2010, 5:12pm

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

r937 · February 9, 2010, 5:24pm

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

system · February 9, 2010, 5:56pm

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 · February 9, 2010, 6:01pm

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

system · February 9, 2010, 6:05pm

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… :x

r937 · February 9, 2010, 6:15pm

now you are just guessing

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

system · February 9, 2010, 7:14pm

Many thanks for your help.
I write two queries and working.

Query 1:

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:

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 · February 9, 2010, 7:58pm

change your second query to this –

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

system · February 10, 2010, 8:38am

r937:

change your second query to this –
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:

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

DarthGuido · February 10, 2010, 8:49am

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 · February 10, 2010, 10:54am

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

please see post #16

system · February 10, 2010, 1:41pm

I’am sorry I dont understand your suggestion… now working

Query 1:

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:

SELECT
     COUNT(DISTINCT code)        
        FROM daTable

Many thanks!