SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Hybrid View

  1. #1
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking 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...

  2. #2
    SitePoint Guru MikeBigg's Avatar
    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

  3. #3
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  4. #4
    SitePoint Guru MikeBigg's Avatar
    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

  5. #5
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Regards,
    Junk

    I am never more keen to learn...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    edit: look at that post count -- 5,555 -- that's four of a kind, and it takes a full house to beat it!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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

  8. #8
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Regards,
    Junk

    I am never more keen to learn...

  11. #11
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Regards,
    Junk

    I am never more keen to learn...

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please give me a moment. I will try explaining to you.
    Regards,
    Junk

    I am never more keen to learn...

  15. #15
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

Posting Permissions

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