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

The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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.