Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: Query to capture aggregates and rowid

    I am using MySQL 4.0.21 trying to write a query that will return a row id value in combination with aggregate functions.

    Goal: Given the sri_student_lexile_history table (see below), return the last quiz for each student with the result set ordered by lexile.

    The fields of interest are the id (key), user_id, time_taken (a unix time stamp) and lexile.

    #----------------------------
    # Table structure for sri_student_lexile_history
    #----------------------------
    CREATE TABLE `sri_student_lexile_history` (
    `ID` int(8) NOT NULL auto_increment,
    `LAST_NAME` varchar(32) NOT NULL default '',
    `FIRST_NAME` varchar(32) NOT NULL default '',
    `GRADE` tinyint(2) NOT NULL default '0',
    `MODIFIED_SEQ_PREV_ID` int(8) default NULL,
    `VALID` char(1) NOT NULL default '1',
    `USER_ID` varchar(32) NOT NULL default '',
    `LEXILE` smallint(6) NOT NULL default '0',
    `TIME_TAKEN` bigint(6) NOT NULL default '0',
    `TIME_INSERTED` bigint(8) NOT NULL default '0',
    `QUIZ_SEQ` int(4) default NULL,
    `SAM_USER_ID` varchar(32) default NULL,
    `TEST_NAME` varchar(32) NOT NULL default '',
    PRIMARY KEY (`ID`)
    ) TYPE=InnoDB COMMENT='SRI Student Lexile History table.';
    #----------------------------
    # Records for table sri_student_lexile_history
    #----------------------------

    insert into sri_student_lexile_history values
    (1, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 814, 1096459679672, 0, null, null, 'test1'),
    (2, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 872, 1096462387299, 0, null, null, 'test2'),
    (3, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 834, 1096471690694, 0, null, null, 'test3'),
    (4, 'Martin', 'Peter', 0, null, '1', 'sriStudent02', 800, 1096471690694, 0, null, null, 'test1'),
    (5, 'Allen', 'Jeffery', 0, null, '1', 'sriStudent01', 135, 1096472787715, 0, null, null, 'test4');

    This is the only way I can think of doing it is using two separate queries with some code in the middle:

    Starting with:

    ID USER_ID LEXILE TIME_TAKEN
    1 sriStudent01 814 1096459679672
    2 sriStudent01 872 1096462387299
    3 sriStudent01 834 1096471690694
    5 sriStudent02 800 1096471690694
    4 sriStudent01 135 1096472787715

    1) select * from sri_student_lexile_history ORDER BY user_id, time_taken DESC;

    This gives me:

    ID USER_ID LEXILE TIME_TAKEN
    4 sriStudent01 135 1096472787715
    3 sriStudent01 834 1096471690694
    2 sriStudent01 872 1096462387299
    1 sriStudent01 814 1096459679672
    5 sriStudent02 800 1096471690694

    Now all of the userIDs are grouped together, and the 1st one is always the latest test. So I go through the result set and grab the ID for each result set where the userID changes. In this case, 4 and 5.

    Then I issue the select:

    SELECT * FROM sri_student_lexile_history WHERE id IN (4,5) ORDER BY lexile DESC;

    Which gives me the final result.

    ID USER_ID LEXILE TIME_TAKEN
    5 sriStudent02 800 1096471690694
    4 sriStudent01 135 1096472787715

    Is it at all possible to write just one query using MySQL 4.0.21?

    If I don't have to get the row id my following query works just fine:
    select user_id,min(lexile) as lexile,max(time_taken) as last_quiz_taken_time
    from sri_student_lexile_history
    group by user_id
    order by lexile desc;

    Thank you much in advance -

    Noga

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t1.id
         , t1.user_id
         , t1.time_taken 
      from sri_student_lexile_history as t1
    inner
      join sri_student_lexile_history as t2
        on t1.user_id
         = t2.user_id       
    group
        by t1.id
         , t1.user_id
         , t1.time_taken 
    having t1.time_taken 
         = max(t2.time_taken)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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