www.webdeveloper.com
Results 1 to 12 of 12

Thread: select unique records with all fields in mysql

  1. #1
    Join Date
    Mar 2009
    Posts
    452

    Unhappy select unique records with all fields in mysql

    hi all, i m getting a problem with a query, i want to select records having unique values in field2 while field1 is primary key...

    so when i run the
    Code:
    SELECT DISTINCT(field2) FROM table
    it goes fine but i get only one field in result.

    when i try this
    Code:
    SELECT DISTINCT(field2),field1,field3,field4,field5,field6,field7,field8,field9,field10 FROM table
    i get all fields but not unique values in field2

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    DISTINCT applies to the entire row, not an individual field. Sounds like maybe you want to do a GROUP BY on the field, perhaps, though I'm not sure that would make sense either -- it all depends on exactly what you want to accomplish.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    PHP Solution:

    PHP Code:

    <?php

    function __autoload($class_name) { require_once('./classes/' $class_name '.php'); }

    try {
        
    $result DB::PDO()->query("SELECT field1, field2, field3 FROM FooTable");
        
    #get all unique column 1
      
    echo '<pre>',
        
    print_r($stmt->fetchAll(PDO::FETCH_COLUMN PDO::FETCH_UNIQUE0)),
        
    #get all unique column 2
        
    print_r($stmt->fetchAll(PDO::FETCH_COLUMN PDO::FETCH_UNIQUE1)),
        
    #get all unique column 3
        
    print_r($stmt->fetchAll(PDO::FETCH_COLUMN PDO::FETCH_UNIQUE2)),
      
    '</pre>';
    } catch (
    PDOException $e) {
      die(
    '<pre>'.$e->getTraceAsString().'</pre>');
    }

    ?>

    <?php

    /**
     * A simple wrapper to make PDO accessible from multiple scopes, NOTE that this 
     * class is more of a singleton wrapper, you should never have an instance of DB
     * stored in a variable.
     * 
     * @author eval(BadCode)
     */
    class DB {
      
    /**
       * A singleton instance of PDO
       * @var PDO
       */
      
    private static $pdo NULL;

      private function 
    __construct() {
        
    $db_args sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s',CONFIG::DATABASE_HOST,CONFIG::DATABASE_PORT,CONFIG::DATABASE_NAME,CONFIG::DATABASE_CHARSET);
        try {
          
    self::$pdo = new PDO($db_args,CONFIG::DATABASE_USER,CONFIG::DATABASE_PASS);
          
    self::$pdo->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
          
    self::$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARESfalse);
        } catch (
    PDOException $e) {
          die(
    "<pre>
          An exception was raised when trying to connect to the database. 
          
          PLEASE NOTE:
          ---------------
          As with any server, there are weekly server restarts and a 
          reserved maintenance window. For [scrubbed] and [scrubbed]
          those times are as follows:

          Weekly Restart: Every Sunday, [scrubbed]
          Reserved Maintenance Window: Every Sunday, [scrubbed]
          ---------------
          
          Here are the details of the exception raised:
          
          
    {$e->getMessage()}
          </pre>"
    );
        }
      }
      
    /**
       * Used to get the singleton instance of PDO.
       * @return PDO
       */
      
    public static function PDO() {
        if(
    self::$pdo === NULL) new DB();
        return 
    self::$pdo;
      }
      
    }

    ?>
    MySQL Solution:

    Code:
    SELECT distrinct(field1) FROM FooTable;
    Also, NogDog. You may want to revisit MySQL's DISTINCT feature in version 5.5, it can be used multiple ways and if used like this SELECT DISTINCT a,b,c you are correct, but I believe it can also be used like this SELECT DISTINCT(a) b,c... I'm not entirely sure what it means and completely agree- using GROUP BY makes more sense.

    Edit: The whole point of the OOP code was to reuse it- so you get the full implementation and not just the snippet.
    Last edited by eval(BadCode); 11-14-2011 at 03:40 PM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  4. #4
    Join Date
    Mar 2009
    Posts
    452
    I actually want to get all records and all fields having unique value in field2

  5. #5
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    SELECT field1,field2,field3,field4,field5,field6,field7,field8,field9,field10 FROM table GROUP BY field2;

    I should warn you.... MySQL lets you do this, but other database languages may not. The reason is because you don't say what it is you want to do with the other 9 fields, it would make more sense to write an aggregate function around them. For example:

    SELECT max(field1), field2, max(field3),max(field4),max(field5),max(field6),max(field7),max(field8),max(field9),max(field10) FROM table GROUP BY field2;

    is not the same as

    SELECT min(field1), field2, min(field3),min(field4),min(field5),min(field6),min(field7),min(field8),min(field9),min(field10) FROM table GROUP BY field2;

    So fields 1,3,4,5,6,7,8,9,10 may contain "unexpected" or inconsistent data. When I say may- you have to know what you're doing, sometimes those other languages pointlessly require an aggregate function, which brings me to my next point. You can specify multiple columns in the GROUP BY clause:

    GROUP BY field1, field2

    would be an example. Now there's no question what field1 and field2 will be, you get the distinct permutations of their values. Well, since field1 is the primary key, you will get every record, so maybe group by field2, field3 instead.
    Last edited by eval(BadCode); 11-15-2011 at 12:33 AM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  6. #6
    Join Date
    Mar 2009
    Posts
    452
    that's gr8... almost what i want, but a little problem now:

    it does not result the original value of field2, i.e. it gives values starting from 1 through 6000

    while the original values are different

  7. #7
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Quote Originally Posted by ZABI View Post
    that's gr8... almost what i want, but a little problem now:

    it does not result the original value of field2, i.e. it gives values starting from 1 through 6000

    while the original values are different
    You're not making sense. Show me what it gives you, and show me what you expected.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  8. #8
    Join Date
    Mar 2009
    Posts
    452
    mmm let me explain simply...

    i am developing an application for students' fee records. on main page i want to show a list of the students, there are also multiple records for a single student, and we dont want to display multiple records for a single student. when user will click on a record it will open a popup with complete history of their fees.

  9. #9
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Oh, I see. Well MySQL is intended to store relational data. If you had normalized your data (a modest expectation for a relation database- at least some level of normalization is expected), you would not need to select distinct.

    Let me explain a little bit more about what I mean:

    Code:
    create table Student (
    Student_ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    firstName VARCHAR(30) NOT NULL,
    lastName VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    CONSTRAINT UQ__StudentEmail UNIQUE (email)
    ) Engine = InnoDB;
    
    create table Fee (
    Fee_ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Student_ID INT UNSIGNED,
    feeAmount INT ZEROFILL,
    feeNotes TEXT,
      INDEX FK__Fee_2_Student (Student_ID),
      FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID)
        ON UPDATE CASCADE 
       ON DELETE CASCADE
    ) Engine = InnoDB;
    Now to get a distinct list of students you simply:
    SELECT * FROM Student;

    Need more information about each student's Fees (a detailed history)?
    SELECT * FROM Student JOIN Fee USING (Student_ID);

    Need only the summary of student fees?
    Code:
    SELECT 
      Student.*,sum(Fee.feeAmount) as totalFees
    FROM Student 
    JOIN Fee USING (Student_ID) 
    GROUP BY Student_ID;
    But if you try to put all of this information into a single table the data will be repetitive- hence the need you have for selecting DISTINCT records. You took a 1:N (one to many) relationship and tried to represent it as 1:1 (one to one).

    Do you understand?
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  10. #10
    Join Date
    Mar 2009
    Posts
    452
    thanx again... the actual problem is these records are exported from another Application in csv format, and i don't know much about that application... nor i have a good picture of their tables structure. so i m left with a bunch of problems.

  11. #11
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    then just leave it as a CSV is you're only going to have 1 table. It's faster that way hands down, no fuss.

    MySQL, i guess, does have a CSV table engine... but why bother, I think that's as useful as a FEDERATED table engine... like how often will I use it? maybe once in my lifetime to test it

    PHP Code:

    list($csv,$uqColumn)  = array(fopen('thecsv.csv','r'),array());
    while(
    $r fgets_csv($csv)) $uqColumn[$r[2]] = $r[2]; //the assumption is the unique column u want is the 3rd column ... $r[2]
    fclose($csv);

    die(
    print_r($uqColumn,true)); //check it 
    edit: and a hella lot less code
    Last edited by eval(BadCode); 11-21-2011 at 07:15 AM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  12. #12
    Join Date
    Mar 2009
    Posts
    452
    intresting... but not what i want :P

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles