Hi there! I wrote the below query to give me results from a table which contains: The most recent time (max(sessionDate)) from the visits made by the user(tom) to the learningObject(lo_1,...lo_n) in a package(SingleCourseEx). //query - 1 String query = SELECT test.STUDENT_ID,test.LEARNING_OBJECT_NAME,MAX(test.SESSION_DATE) FROM SESSION AS test WHERE test.PACKAGE_ID='SingleCourseEx' And test.STUDENT_ID='tom' GROUP BY test.LEARNING_OBJECT_NAME,test.STUDENT_ID this query gives me proper results: tom: Conduct of Vessels in Sight of One Another : 2003-01-07 12:00:36 tom: Exam : 2003-01-07 12:10:33 tom: Lights & Shapes : 2003-01-07 12:12:45 --------------------------------------------------------- Now I want to get the other details(sessionID) of that rows which are unique (that means I cant ask for them in my above query as they are aggregated) So I wrote the below query(using my query-1 as a subquery)and it doesn't work the way I wanted.. results are: tom:4:Conduct of Vessels in Sight of One Another : 2003-01-07 12:00:36 tom:24:Conduct of Vessels in Sight of One Another : 2003-01-04 10:00:36 tom:2:Exam : 2003-01-07 12:10:33 tom:26:Lights & Shapes : 2003-01-07 12:12:45 tom:22:Exam : 2003-01-09 14:10:33 ..... .... That means it's picking up all rows
Query - 2 with the above subquery SELECT original.STUDENT_ID,original.SESSION_ID,original.LEARNING_OBJECT_NAME, original.SESSION_DATE FROM SESSION AS original WHERE EXISTS (SELECT test.STUDENT_ID,test.LEARNING_OBJECT_NAME,MAX(test.SESSION_DATE) FROM SESSION AS test WHERE test.PACKAGE_ID='SingleCourseEx' And test.STUDENT_ID='tom' GROUP BY test.LEARNING_OBJECT_NAME,test.STUDENT_ID) All I wnated to do here is Select "all/rest of the columns" from session where student_id, loName, session_date matches the results of my sub query. I could use "IN" operator if the matching is only for one field, but don't know how to match multiple fields. I am using Access as my database. Any ideas on this?? Any suggestions would be of great help. -M
Michael Matola
whippersnapper
Ranch Hand
Joined: Mar 25, 2001
Posts: 1721
posted
0
Several things 1. Are you sure of the results you provided from the first query? I would think the query would return the following. tom: Exam : 2003-01-09 14:10:33 and not tom: Exam : 2003-01-07 12:10:33 Are you by any chance storing your dates as text fields? 2. For questions like these, in general it's really helpful if you give the key structure of the table(s) in question. 3. That said, based on some assumptions I've made about your table, I think the query you're looking for is something along the lines of the following. (If not, you'll have to provide some more details about what you're trying to do.)
Adjusting fieldnames, of course. This query rests on the assumption that your table has 3 key fields: studentId, learningObjectName, and sessionDate. The correlated subquery returns the maximum sessionDate for what is essentially a grouping by studentId and learningObjectName, only the "grouping" is done not with the "group by" clause, but with a join to the enclosing table.
4. Please take another look at the JavaRanch Naming Policy. We'd like to have you continue posting here but "nmhb 23" doesn't comply with the naming policy, so please update your display name. You can do so here. Happy 'ranching.
Michael Matola
whippersnapper
Ranch Hand
Joined: Mar 25, 2001
Posts: 1721
posted
0
If this indeed solves your problem, you might want to take a look at this other post which goes into a little more detail on this kind of querying. [ January 14, 2003: Message edited by: Michael Matola ]
0
