Select distinct Max

[rob51383]

I have a table with the following pertinant fields:

completed
quarter

For each quarter there will be 10 records (40 records per year). A date will be inserted when each of the 10 requirements are fullfiled.

What I want to do is get the last date the 10 requirements are completed so I can see when the querterly is completed.

1. completed will always be a date
2. quarter will look like "1:2005", "2:2005", "3:2005", "4:2005", "1:2006"


So I want to:

1. Select quarterly
2. Select completed
3. Select distinct quarterly where MAX completed

Problem is "MAX" only returns 1 record, I want to return 1 record for each distince quarter.

 

[rob51383]

Ok, I think this will work but this method is only supported from MySql 4.1+... I am running 3.23.58!

SELECT distinct `quarter` from `quarterly` where `completed` = (select max(`completed`) from `quarterly`)

 

[TonyGroves]

You could use a temporary table instead of your sub-query; something like:
[tt]
create temporary table t as
select max(completed) completed from quarterly;

select distinct quarter
from quarterly,t
where quarterly.completed=t.completed;

...

drop table t;
[/tt]

 

[rob51383]

Looks like there is a problem with that though... The first query would only select one record from the table!

I will have 10 records for each quarter, 4 quarters per year. On top of that there is about 1,000 people that each need to be verified quarterly.

What I need to do is select all 10 records where quarter = 'Selected Quarter'. Then select the max `completed` from those.

So with your method it would be:

create temporary table t as
select * from `quarterly` where `userid`='1' and `quarter`='1:2005';

select MAX(`quarter`)
from quarterly,t;

drop table t;

Only problem with this is 10 people can run this query at the same time and there can be a problem. There will be over 1,000 users operating the system, all of them on this section of the program during the same 8 hours of quarterly week.

Untill I get the MySql Version updated I just inserted an 11'th record that is created when the final object is verified. I then select this record based on an ID to get the overall completion date.

Thanks for the help anyways!

 

[r937]

are you saying that the 10 rows per quarter will always be there, but that if some of the rows aren't complete, the completed column will be null?

and you want the latest non-null completed per quarter?

is that it?

r937.com | rudy.ca

 

[rob51383]

Yes, I think you understood correctly.

Quarterly Verification occurs around the same week every quarter. There is a function the pushes 10 (now 11) records into the table for each person when the administrator decides to make the verifications available to begin.

Every record is NULL to begin with. A member can verify 2 records per day for 1 week or all 10 in one day, it is up to them.

The administrator does not want to see all 10 records for each person, he just wants to know when and on what day all 10 records have been verified.

So if someone completes 9 on Monday 18 July he will not show as completed. Then say he completes number 10 on Friday 22 July, his verification process is complete on 22 July.

All 10 records have an "SSN" (Social Security Number) and "Quarter" colum so I will need to first:

select * where quarter="SELECTED QUARTER"
Then:
Select 1 record for each "SSN" but select the record that has the highest "completed" date



I found it easier just to use the query I allready run to show the member what he still needs to verify to check if 9 of 11 records are completed (process is done in CGI while I am looping through the query results "$i++").

If 9 of 11 are complete, when he updates record 10, record 11 will also update with the current date. The table has a "value" column so when I need to print a report of who is done/not done I search the table "where value="record 11", or I can also search "where value is NULL" to get a report of the slackers...

 

[r937]

I found it easier just to use the query I allready run

so, are you doing what you need to do, or are you still stuck?

r937.com | rudy.ca