Link to home
Start Free TrialLog in
Avatar of Troder
Troder

asked on

Mysql return results by date field

I have a database that is full of publications broken into individual chapters, bibliographies etc..

I need to return a list of COMPONENT_TITLE, COMPONENT_AUTHOR  for each STOCK_NO based on the most recent PRINT_DATE..
currently when i do a select of the components i get double results for any publication that has two entries with the same STOCK_NO
the key fields for this are the STOCK_NO and PRINT_DATE. all other fields will be different
so there could be two sets of data under STOCK_NO='BOOK_43' with different PRINT_DATE for example one with PRINT_DATE='2007-01-01'
and another where PRINT_DATE='1997-04-02'
I would like to return TITLE and AUTHOR for only the results with the most recent PRINT_DATE..

Also as a side question if I have a database with no autonumber field or way to sort is there a way to return the last entry/or most recent row in the database?

Thanks
Avatar of Bernard Savonet
Bernard Savonet
Flag of France image

Question 1:
You might test the impact of your current SELECT query where you would say instead SELECT DISTINCT
see http://dev.mysql.com/doc/refman/5.0/en/select.html

I guess your query is something like
SELECT COMPONENT_TITLE, COMPONENT_AUTHOR, MAX(PRINT_DATE)
FROM MYTABLE
 GROUP BY STOCK_NO

Question 2
Having table without a primary key is not recommended.... and if you have a primary key you can find the MAX of the correponding column. Which might or might not be the last entry in the table.
It seems there is no other way to wind the last record inserted...
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...Question 1:
You might test the impact of your current SELECT query where you would say instead SELECT DISTINCT
see http://dev.mysql.com/doc/refman/5.0/en/select.html

I guess your query should be something like
SELECT DISTINCT COMPONENT_TITLE, COMPONENT_AUTHOR, MAX(PRINT_DATE)
FROM MYTABLE
 GROUP BY STOCK_NO
Avatar of Troder
Troder

ASKER

ee_rlee that is exactly what I wanted to do thanks!
you're welcome ;)