SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
-
Jan 11, 2005, 12:37 #1
Return ref_no once even if there are multiple records with the same ref_no
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.Regards,
Junk
I am never more keen to learn...
-
Jan 11, 2005, 12:44 #2
- Join Date
- Jun 2004
- Location
- Reading, UK
- Posts
- 970
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
if you only want the ref_no, you could do something like
select distinct ref_no from ... where ...
Mike
-
Jan 11, 2005, 12:51 #3
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!Regards,
Junk
I am never more keen to learn...
-
Jan 11, 2005, 12:54 #4
- Join Date
- Jun 2004
- Location
- Reading, UK
- Posts
- 970
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
the distinct word will limit it to only one of each rec_no
Try it and see.
Mike
-
Jan 11, 2005, 13:09 #5
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. ThanksRegards,
Junk
I am never more keen to learn...
-
Jan 11, 2005, 17:21 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code: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
-
Jan 27, 2005, 23:10 #7
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!Regards,
Junk
I am never more keen to learn...
-
Jan 27, 2005, 23:20 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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?
-
Jan 27, 2005, 23:26 #9
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?
ThanksRegards,
Junk
I am never more keen to learn...
-
Jan 27, 2005, 23:30 #10
- Join Date
- Jun 2004
- Location
- Reading, UK
- Posts
- 970
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
Do I win?
Regards,
Mike
-
Jan 27, 2005, 23:30 #11
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)
ThanksRegards,
Junk
I am never more keen to learn...
-
Jan 27, 2005, 23:32 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
yes, you have failed to give the derived table an alias
change it to this --Code: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
-
Jan 27, 2005, 23:34 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 27, 2005, 23:39 #14
Please give me a moment. I will try explaining to you.
Regards,
Junk
I am never more keen to learn...
-
Jan 28, 2005, 00:02 #15
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..Regards,
Junk
I am never more keen to learn...
Bookmarks