jeudi 7 janvier 2016

SQLITE Query For Composite Data to show in one column for the identical collumn value

I have to implement one complex Query in SQLITE but as don't have much proficient knwoledge in SQLITE.

I have One Table named as PupilTestAnswers in which i have composite primary Key

My Schema is as follows:

CREATE TABLE PupilTestAnswers (
  testQuestionId integer NOT NULL,
  pupilId integer NOT NULL,
  testId integer NOT NULL,
  score integer NOT NULL DEFAULT(-2),
  lastUpdated text,
  isMyScore integer NOT NULL,
  isUploaded integer NOT NULL,
  PRIMARY KEY(testQuestionId, pupilId)
);

now what i my data look a like is as follows : enter image description here

Now What Data I want is that it will give me list of Data By this should be the unique Combination in result.

e.g.

18 |   3 | 2 | 1 | 2016-01-06T06:13:50.000Z | 1 | 0
16 | 154 | 2 | 0 | 2016-01-06T06:13:50.000Z | 0 | 0

for above tow row as their LastUpdate values are same for the same testId so i need only one row in result as follows for this row and those rows testId and Last Update are diffrent then i need seprate row for them

18,16 | 3,154 | 2 | 1,0 | 2016-01-06T06:13:50.000Z | 1,0 | 0,0

So aboe thing i want to achieve in SQLITE .

I have tried this in SQLServer but i cant convert the query in SQLITE.

Aucun commentaire:

Enregistrer un commentaire