Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 3 of 3
  1. #1
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    Hi All.

    I dont even know that distinct is the way to go with this one.

    I have a table like this:

    Code:
    CREATE TABLE `prices_control` (
      `id` int(11) NOT NULL auto_increment,
      `code` varchar(8) NOT NULL default '',
      `value` decimal(19,3) NOT NULL default '0.000',
      `updated` datetime default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM;
    Data in the table looks like this:

    Code:
    1,ABC,5.000,2006-03-15 16:53:06
    There are thousands of records in this table on a daily basis.

    I need to pull out entire rows based ordered by the latest datetime value.
    further to that i need to only get records for distinct codes (one row for each code)

    short of writing nasty php can someone suggest a mysql solution that does what i need/want.

    thanks, much appreciated

  2.  

  3. #2
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    There might be other ways of achieving this, but the following subselect works.
    Code:
    select * from (select * from prices_control order by updated desc) as result group by code
    The first select (in parenthesis) orders the rows after dates. The second returns unique 'code' entries and presents them in the order of the first select query.

    If your server doesn't support subselects, you can achieve the same by using temporary tables (they will be erased from memory when the db connection is closed)
    Code:
     create temporary table list select * from prices_control order by updated desc;
    select * from list group by code
    S. Rosland

  4. #3
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    thanks heaps for your help, really appreciate it, both servers i use (mysql 3.23.x and 4.1.x) like the query.

    I totally forgot about temporary tables !

    thanks again.

    edit: i lie, the query doesnt work on 3.23.x (pma was being silly) lucky im not using that db.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 11:15 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com