Hello,
I've see a post on forums about this error :
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target
list
This append when using 1.3.2 version on postgres Database. This is the
SQL code that offense Pg :
SELECT DISTINCT(th.ticket_id)
FROM ticket_history th
WHERE th.create_time <= '2004-10-31 23:59:59'
AND th.create_time >= '2004-10-01 00:00:01'
ORDER BY th.create_time DESC LIMIT 50000
This is because Pg dont do some ugly guess about data that can be
different. For exemple, if there are multiple records with same ID at
same timestamp (ok, in real life, this cannot append, but in database
...) , witch one retrieve for DISTINCT ?
So, it's possible to change this, with the use of an aggregate function
(in this case, why not MAX ?) and then GROUP record with the same ID,
eliminate the use of DISTINCT.
SELECT th.ticket_id
FROM ticket_history th
WHERE th.create_time <= '2004-10-31 23:59:59'
AND th.create_time >= '2004-10-28 00:00:01'
GROUP BY th.ticket_id
ORDER BY max(th.create_time)
LIMIT 50000
IMO, this is a more CLEAN code (may be better with sub query) :-)
--
Alexandre
--
___________________________] YCOM SA [____________________________
Alexandre Ghisoli Directeur Technique
Rue Galilée 15 1400 Yverdon-les-Bains
Tel: +41 (024) 423 92 75 Fax: +41 (024) 423 91 87
_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Support oder Consulting für Ihr OTRS System?
=> http://www.otrs.de/
