Hi Helen,
we did have activity_label in the select clause but it hasn't seemed to work

like so...

SELECT DISTINCT activity_id, activity_date, activity_label

we've ended up using a group by on these columns instead...

Thanks.

Adam Thick, Jenni Bennett.






Helen Borrie wrote:

>>At 11:25 AM 23/03/2006, you wrote:
>>
>>
>>>Hi everyone,
>>> we're having a spot of trouble with a query.
>>>
>>>As you can see below we're selecting data from a view using distinct and
>>>order by...
>>>
>>>SELECT DISTINCT activity_id, activity_date
>>>
>>>
>>>FROM VIEW_NAME
>>
>>
>>>ORDER BY activity_date ASC, activity_label
>>>
>>>This doesn't return rows ordered by activity_date...
>>>
>>>Little help?
>>>
>>>
>
>At 02:43 PM 23/03/2006, I wrote:
>
>
>
>
>>Ummm...is activity_label actually a column in the view?
>>
>>What happens if you either 1) take out activity_label from the order
>>by clause or 2) add activity_label to the select list?
>>
>>
>
>Errm, moreover, I neglected to comment that trying to order a
>DISTINCT query by a column that is not in the DISTINCT spec is
>illogical. By its very nature, DISTINCT eliminates all rows that
>contain duplicate instances of the value mix specified in the
>SELECT. That's what DISTINCT is *designed* for. Any columns not
>used for distinguishing which rows are selected are irrelevant and
>take no further part in the logic.
>
>The row selected will be the first (in no order) that matches the
>DISTINCT specification. The final set of distinct rows will have no
>way to relate to any particular activity_label, since it's not
>involved in the distinguishing logic.
>
>So work out what you really want in your output and re-cut the cloth.
>
>./heLen
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>

--
Adam Thick

Strategic Data Pty Ltd

Ph : +61 (3) 9348-2013
Fax: +61 (3) 9348-2015
Mob: 0404 032 763
Email: Adam.Thick@...
Web: http://www.StrategicData.com.au/
Post: P.O. Box 4262
Melbourne University, VIC 3052
Australia