Return ref_no once even if there are multiple records with the same ref_no

[Junk]

Hi,

I need some help here.

Currently I have this sql statement

$sql_reviewing = "SELECT * FROM EVALUATION_TABLE where status in ('Reviewing') and expiry_date < '$today' order by ref_no asc";

ref_no is the number of a paper and this paper can be reviewed by a few reviewers. For any particular paper, some reviewers might have reviewed (status) the paper while some have not. Right now, I want to extract the paper info with the ref_no where at least one reviewers has not reviewed the paper (as such status remains at reviewing).

The above sql will return multiple times of the same ref_no. However, what I need is for the ref_no to be returned once.

Return ref_no once even if there are multiple records with the same ref_no

Which means that if there are 2 or more reviewers who are still reviewing the same particular paper (same ref_no), this ref_no will be selected once only.

Hope that is clear.

Thanks and any advise is most appreciated.

[MikeBigg]

if you only want the ref_no, you could do something like

select distinct ref_no from ... where ...

Mike

[Junk]

Hi,

but that will return multiple times of the same ref_no as well.

Well, each record has a ref_no and this ref_no can be the same for a few records. As such, I want the sql to return ref_no once even if there are multiple records with the same ref_no are selected.

Do you get what I mean?

Thanks alot!

[MikeBigg]

the distinct word will limit it to only one of each rec_no

Try it and see.

Mike

[Junk]

Hi Mike,

haha forgottened abt distinct. Thanks for reminding.

Anyway, I have another query. What if the situation now is that for records with the same ref_no, I need to find the "latest" expiry date among these records and ensure that this date is < today's date? And I need the distinct ref_no for these records.

Please advise. Thanks

[r937]

select ref_no
     , max(expiry_date) as latest_expiry_date
  from evaluation_table 
 where status  = 'Reviewing'
   and expiry_date < current_date
group
    by ref_no
order
    by ref_no asc

edit: look at that post count -- 5,555 -- that's four of a kind, and it takes a full house to beat it!!

[Junk]

Hi,

Thanks for your suggestion. I have edited the sql and this is how it goes:

$sql_expired = "select ref_no, title, max(expiry_date) as latest_expiry_date from EVALUATION_TABLE where status = 'Not Reviewed' and ref_no in (select ref_no from EVALUATION_TABLE where max(expiry_date) < '$today') group by ref_no order by ref_no asc";

I need to find the max(expiry_date) --from separate records yet with the same ref_no to be less than today.

Any advises? Thanks!

[r937]

i'm sorry, you will have to explain that a bit more

the query you wrote is invalid, you can't say WHERE MAX(xxx)

did you try my query? was it not what you expected? why not?

[Junk]

Hi,

Thanks for your fast reply. I am thinking of using this

$sql_expired = "select ref_no, title, latest_expiry_date from (select ref_no, title, max(expiry_date) as latest_expiry_date from EVALUATION_TABLE where status = 'Not Reviewed' group by ref_no order by ref_no asc) where latest_expiry_date < '$today'";

but this is the error

Every derived table must have its own alias

May I know where have I gone wrong?

Thanks

[MikeBigg]

edit: look at that post count -- 5,555 -- that's four of a kind, and it takes a full house to beat it!!

I have a full house ... wife and 4 kids Do I win?

Regards,

Mike

[Junk]

Oh, I need to satisfy this condition max(expiry_date)<'$today' instead of expiry_date < '$today' you see.

I need to ensure that the expiry dates for all the reviews to be less than today before I can conclude that the manuscript is expired (in other words, all the reviews have expired)

Thanks

[r937]

yes, you have failed to give the derived table an alias

change it to this --

select ref_no
     , title
     , latest_expiry_date 
  from (
       select ref_no
            , title
            , max(expiry_date) as latest_expiry_date 
         from EVALUATION_TABLE 
        where status = 'Not Reviewed' 
       group 
           by ref_no 
            , title
       ) as dt
 where latest_expiry_date < current_date
order 
    by ref_no asc 

[r937]

i'm sorry, i'm not keeping up with you

"I need to ensure that the expiry dates for all the reviews to be less than today before I can conclude that the manuscript is expired (in other words, all the reviews have expired)"

well, that's different

the answer to that was given in post #6

i thought you had changed the requirement and didn't want that solution

[Junk]

Please give me a moment. I will try explaining to you.

[Junk]

Hi,

For every manuscript, reviews are sent to reviewers for evaluation.

ref_no is the id for each manuscript and status is the status of each review.

this is more or less how the EVALUATION_TABLE looks like:

ref_no status expiry_date
12345 Not Reviewed 21-01-2005
12345 Reviewed 14-01-2005
12345 Reviewed 30-01-2005

today=28-01-2005:
From the above case, since the max(expiry_date) is 30-01-2005, all the reviews are considered not expired yet.

today=31-01-2005:
From the above case, since the max(expiry_date) is 30-01-2005, all the reviews are considered expired.

After the latest expiry date has passed, I need to see whether ALL the reviews are Reviewed (from the status). If so, the manuscript is reviewed.

Else (if some reviews are Not Reviewed), the manuscript is expired.


I need to get the distinct ref_no, title and the latest expiry date (30-01-2005) for manuscript that has been expired.

Hmm....haha...guess that should be it.

The ball is in your court now...hit it back yeah..