select unique records with all fields in mysql

[ZABI]

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

SELECT DISTINCT(field2) FROM table

it goes fine but i get only one field in result.

when i try this

SELECT DISTINCT(field2),field1,field3,field4,field5,field6,field7,field8,field9,field10 FROM table

i get all fields but not unique values in field2

[NogDog]

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.

[eval(BadCode)]

PHP Solution:

<?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_UNIQUE, 0)),
    #get all unique column 2
    print_r($stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE, 1)),
    #get all unique column 3
    print_r($stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE, 2)),
  '</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_ERRMODE, PDO::ERRMODE_EXCEPTION);
      self::$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    } 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:

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.

[ZABI]

I actually want to get all records and all fields having unique value in field2

[eval(BadCode)]

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.

[ZABI]

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

[eval(BadCode)]

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.

[ZABI]

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.

[eval(BadCode)]

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:

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?

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?

[ZABI]

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.

[eval(BadCode)]

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

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

[ZABI]

intresting... but not what i want :P