SQL Expert

I need this query to call from PHP:

From a database having

id question_id set_id user_id answered created

I would like to perform:
from all question take those tho have user_id = x and set_id = y
then: for all found number choose only one (the last in time) (distinct question_id)
then: from this list take only the one which has answered = 2 and count how many question are in this list.
let's call this number count1
(this was still easy to do)

Now comes the complicated part (at least for me)

from all question take those tho have user_id = x and set_id = y
then: for all found number choose only one (second last in time) (distinct question_id)
then: from this list take only the one which has answered = 2 and count how many question are in this list.
let's call this number count2

and at the end:

from all question take those tho have user_id = x and set_id = y
then: for all found number choose only one (third last in time) (distinct question_id)
then: from this list take only the one which has answered = 2 and count how many question are in this list.
let's call this number count3

This would be pretty good. It is is possible I would also let the database make this calculation:
count1*3+count2*2+count3*1

You may have better idea to get this calculation done like
for each question get the tree newest (last in time) entry and make the multiplication as I showed => sum up everything

To pay attention:
- you do not know how many different question_id there is in a set
- you will never have them all (like id 23, 24, 30, 80)
- you will not always have tree age level

SQL for testing:

-- phpMyAdmin SQL Dump
-- version 3.3.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generato il: 23 apr, 2012 at 01:14 PM
-- Versione MySQL: 5.5.9
-- Versione PHP: 5.3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `stats`
--

-- --------------------------------------------------------

--
-- Struttura della tabella `answers`
--

CREATE TABLE `answers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`set_id` int(10) unsigned DEFAULT NULL,
`question_id` int(10) unsigned DEFAULT NULL,
`answered` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=31 ;

--
-- Dump dei dati per la tabella `answers`
--

INSERT INTO `answers` VALUES(1, 1, 1, 1, '1', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(2, 1, 1, 2, '2', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(3, 1, 1, 3, '1', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(4, 1, 1, 4, '2', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(5, 1, 1, 5, '2', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(6, 1, 1, 6, '1', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(7, 1, 1, 8, '2', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(8, 1, 1, 10, '2', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(9, 1, 1, 11, '1', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(10, 1, 1, 12, '1', '2012-04-23 13:07:04');
INSERT INTO `answers` VALUES(11, 1, 1, 1, '2', '2012-04-23 13:07:31');
INSERT INTO `answers` VALUES(12, 1, 1, 2, '2', '2012-04-23 13:07:31');
INSERT INTO `answers` VALUES(13, 1, 1, 3, '1', '2012-04-23 13:07:31');
INSERT INTO `answers` VALUES(14, 1, 1, 4, '2', '2012-04-23 13:07:31');
INSERT INTO `answers` VALUES(15, 1, 1, 5, '1', '2012-04-23 13:07:31');
INSERT INTO `answers` VALUES(16, 1, 1, 6, '1', '2012-04-23 13:07:31');
INSERT INTO `answers` VALUES(17, 1, 1, 7, '2', '2012-04-23 13:07:31');
INSERT INTO `answers` VALUES(18, 1, 1, 9, '1', '2012-04-23

Search All Jobs:

Job Details

  • Job Type
  • Fixed Price Job
  • Budget
  • Max $10
  • Start Date
  • 04/23/2012 11:24:27 AM
  • Job Status
  • Job Expired