Re^2: Help with manipulating data from a database??

Ummm... You know you can GROUP BY multiple fields, right?

SELECT stop_reference, service_id, min(distance)
FROM table
GROUP BY stop_reference, service_id ;
[download]

The basic rule is that you can include as many fields in a summary query as you like, provided that each one of them has either a summary operation (such as min()) or a GROUP BY applied to it. (This does rule out anything like SELECT * without going to a subquery, but, then, I only use SELECT * when I'm querying by hand anyhow. Using it in my code carries too much risk of breakage if the table's columns get reordered.)

Comment on Re^2: Help with manipulating data from a database?? Select or Download Code

Replies are listed 'Best First'.
Re^3: Help with manipulating data from a database?? by ForgotPasswordAgain (Vicar) on Jul 03, 2007 at 08:36 UTC
I thought about the double GROUP BY, but I think that'd only work if stop_reference is an ID-like column, with distinct values, right? I was assuming that since stop_reference is referring to bus stops, that the same stop_reference might occur more than once (for example where two or more bus lines intersect). In that case, I think the GROUP BY would no longer work because you'd "group away" some extra rows, right?	[reply]

Replies are listed 'Best First'.

Re^3: Help with manipulating data from a database??
by ForgotPasswordAgain (Vicar) on Jul 03, 2007 at 08:36 UTC

I thought about the double GROUP BY, but I think that'd only work if stop_reference is an ID-like column, with distinct values, right? I was assuming that since stop_reference is referring to bus stops, that the same stop_reference might occur more than once (for example where two or more bus lines intersect). In that case, I think the GROUP BY would no longer work because you'd "group away" some extra rows, right?

[reply]