Member Avatar for showman13

Hi All,

I'm having difficulty in getting the data that I need from the DB.. and it is probably my lack of understanding in the query that I created...

data looks like this in the table called RandPtrack


rec_id type group_id open_date close_date orders shares order_date order_num
1 R 96 2012-02-14 22:55:05 2012-02-19 16:44:31 4 25 2012-02-19 16:44:31 123123
2 P 44 2012-02-14 22:55:58 0000-00-00 00:00:00 0 0 0000-00-00 00:00:00 0
4 R 97 2012-02-19 16:44:45 0000-00-00 00:00:00 0 0 0000-00-00 00:00:00 0

$sql_r="SELECT MAX(group_id), rec_id FROM RandPtrack where type='R'";
$result_r=mysql_query($sql_r);
$request_r=mysql_fetch_array($result_r);
$reserve=$request_r[0];
$reserve_rec=$request_r[1];

What I'm expecting to see is the Max group_id, where the type is R, which would be 97.

And the rec_id of 4 which is connected to the record with the max group_id

What I'm getting is the 97 for the max, but I'm getting rec_id 1...


So, what do I need to do to get the rec_id of the record that has the Max group_id ?

Any suggestions would be very helpful

Thanks in advance.

Douglas

Edited by showman13 because: n/a
  • 2 Contributors
  • 2 Replies
  • 261 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by showman13

Dani AI

Generated

's approach is the simplest practical fix, and it also sidesteps the root cause of the surprising rec_id: selecting an aggregate (MAX(group_id)) and a non‑aggregated column in the same SELECT without GROUP BY yields an indeterminate value for the non‑aggregated column in MySQL. That is why saw a rec_id that didn't match the max group_id. Newer MySQL modes (ONLY_FULL_GROUP_BY) will reject that query instead of returning an undefined value.

Two reliable patterns that make the intent explicit:

  • To return every row that has the maximum group_id (handles ties):

    SELECT r.rec_id, r.group_id
FROM RandPtrack r
JOIN (
SELECT MAX(group_id) AS max_gid
FROM RandPtrack
WHERE type = 'R'
) m ON r.group_id = m.max_gid
WHERE r.type = 'R';

  • To return a single, deterministic row when there may be ties (choose a tie‑breaker):

    SELECT rec_id, group_id
FROM RandPtrack
WHERE type = 'R'
ORDER BY group_id DESC, rec_id DESC
LIMIT 1;

Performance and correctness notes: add a composite index on (type, group_id) so the MAX scan or ordered LIMIT can use the index rather than doing a full table sort. If only one rec_id is required when multiple rows share the max, decide the tie rule up front (MIN or MAX rec_id, or an ORDER BY on another column) so results are deterministic.

Recommended Answers

All 2 Replies

Member Avatar for rch1231

Try this sql statement instead:

"SELECT group_id, rec_id FROM RandPtrack where type='R' order by group_id desc limit 1"

This will select all records sort them in descending group_id order (with the largest group_id first) and them limit the output to one (the first) record.

Member Avatar for showman13

Try this sql statement instead:

"SELECT group_id, rec_id FROM RandPtrack where type='R' order by group_id desc limit 1"

This will select all records sort them in descending group_id order (with the largest group_id first) and them limit the output to one (the first) record.

That was WAY too easy... Generally that is the answer. The easy one...

Thank you very much. I do have a tendency to make things harder than they need to be.

Again. Thanks, I'm sure that will do what I need.

Douglas

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.