Find one row for each time and each id where field1 is max

[MsChelle]

I hope someone out there can help me.

I have a table that has date, time, cellid, and 25 other numeric data fields (call them f1, f2, etc). There are entries for each cellid, every halfhour, every day.

I need to find the max(f1) and list all the data within that row for each cellid.

Example table

1234, 7/25/2004, 00:00, 134, 3525, 235652, 234, 115...
1234, 7/25/2004, 00:30, 1244, 4546,2342, 35252, 1555...
1235, 7/25/2004, 00:00, 44454, 4444, 6572, 453434, 445...
1235, 7/25/2004, 00:30, 23525, 222, 351, 3525, 3234....
1236, 7/25/2004, 00:00, 45422, 2334, 11564, 12345, 12313....
1236, 7/25/2004, 00:30, 456467, 4564, 1111, 12344, 12345....

Example results

7/25/2004, 1234, 00:30, 1244, 4546, 2342, 35252, 1555...
7/25/2004, 1235, 00:00, 44454, 4444, 6572, 453434, 445...
7/25/2004, 1236, 00:30, 456467, 4564, 1111, 12344, 12345...

There are ~3500 different cellids (it varies) and 48 halfhour entries per day per cellid. Since I don't want the max value across the whole day of all the fields, I can't use max on the other fields, and if I use them in the group by, I get multiple rows of data.

Can anyone help?

 

[twifosp]

In your example each max field you want return is on the half hour... is this true of all your data? If so, that's your answer.

If not, then you're on the right track by using group by. Look up the HAVING clause in books online.

having max(f1)

 

[ESquared]

sounds like you have the group by covered, you just need to know how to get the rest of the data joined.

The question is, what does the rest of the data look like that causes a group by on all fields to return multiple rows? It must be different, so, what criteria do you use to select which row's data you want to see?

1234, 7/25/2004, 00:00, 134, 3525, 235652, 234, 115, [red]George[/red]
1234, 7/25/2004, 00:30, 1244, 4546,2342, 35252, 1555, [red]Betty[/red]

--->

7/25/2004, 1234, 00:30, 1244, 4546, 2342, 35252, 1555, [red]???[/red]


Also, I think 2342 is not larger than 235652, was that just a mistake in your example?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[MsChelle]

Sorry, I actually listed the fields wrong. In the example data I have cellid, date, time, f1, f2... In the example output I have date, time, cellid, f1, f2....

The results I want would be the max of f1 among all 48 halfhour periods, returning the other data on that halfhour for that cellid.

This is what I came up with:

SELECT e11.cellid, e11.time_val, e11.i3_tch_c_secs,
e11.CR_HO_ATTEMPT,e11.CR_HO_REQ,e11.CR_MEAS_INQ,e11.CR_TRIG_TIM,e11.DATE_VAL,e11.DIS_I6_TCH_C_SECS,
e11.DOWNLINK_MEAN_SQE_NBR,e11.D_Q_TIME_TIMES,e11.D_Q_TIME_TOTAL,e11.D_TCH_FAILED,
e11.D_TCH_HOLD_TIMES,e11.D_TCH_HOLD_TOTAL,
e11.D_TCH_QUEUED,e11.D_TCH_REQS,e11.HO_FAIL_CR3,
e11.H_CHAN_ALLOC_3,e11.H_CHAN_ALLOC_6,e11.H_FAILED_RSRC_3,
e11.H_FAILED_RSRC_6,e11.H_FAILED_THRES_3,e11.H_FAILED_THRES_6,e11.I12_NONREG_C_SECS,e11.I12_REG_C_SECS,
e11.I12_TIME_IDLE,e11.I3_TCH_C_SECS,e11.I3_TIME_IDLE,
e11.I6_TIME_IDLE,e11.PCH_I12_CENTI_SECS,
e11.SUBR_BLK_FIRST_AT,e11.TEL_I6_TCH_C_SECS,
e11.T_DCCH_FAILED,e11.T_DCCH_HOLD_TIMES,
e11.T_DCCH_HOLD_TOTAL,e11.T_DCCH_REQS,e11.T_LOST_CALLS_3,
e11.T_LOST_CALLS_6,e11.T_LOST_CALLS_NON_RF,
e11.T_TCH_FAILED_3,e11.T_TCH_FAILED_6,
e11.T_TCH_HOLD_TIMES_3,e11.T_TCH_HOLD_TIMES_6,
e11.T_TCH_HOLD_TOTAL_3,e11.T_TCH_HOLD_TOTAL_6,
e11.T_TCH_QUEUED_3,e11.T_TCH_QUEUED_6,e11.T_TCH_REQS_3,
e11.T_TCH_REQS_6
FROM ecell e11 INNER JOIN
(SELECT cellid AS Expr1, MAX(i3_tch_c_secs) AS Expr3
FROM ecell b11
WHERE date_val = #thisdate# and i3_tch_c_secs > 0
GROUP BY b11.cellid) b11 ON b11.Expr1 = e11.cellid AND b11.Expr3 = e11.i3_tch_c_secs

WHERE e11.date_val = #thisdate#
ORDER BY e11.cellid


(btw I'm using coldfusion, which is why the date parameter is writting like #thisdate#)

It seems to work pretty well, but if anyone has a better way of doing it I'd appreciate it.

 

[ESquared]

For what it's worth, putting each field on its own line with an indent to set them off from the other parts of the query would improve readability into at least the 'no eye strain' level. The formatting looks like Enterprise Manager's, a little.

Anyway, I wasn't asking youa bout the max of F1. I was asking how you intend to choose ONE row for the remaining data. In your query you are joining on cellid and i3_tch_c_secs. But, if you are getting more results than you want, something has to change. At this point I'm unclear what you want.

Let me first ask, is this query you've given not working for you? And if not, what's wrong?

-------------------------------------
A sacrifice is harder when no one knows you've made it.