IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

IWETHEY Home / IWETHEY Board / Databases Forum / One solution
Post #204,255
by JayMehaffey
4/21/05 10:25:00 AM
Reply
New One solution
(SELECT distinct O.orderdate, O.ordernum, 'x' as OpenEscalations
from tbl_order O
INNER JOIN tbl_escalation E on O.ordernum = E.ordernum
where E.escalation = 'open')
UNION ALL
(SELECT distinct O.orderdate, O.ordernum, ' '
from tbl_order O
LEFT JOIN tbl_escalation E on O.ordernum = E.ordernum and E.escalation = 'open'
where E.escalation IS NULL)
order by OpenEscalations desc, ordernum

Not the neatest solution, but as long as the data you are looking at is not huge it should be fast.

Jay
Post #204,380
by drewk
Picture of drewk
4/21/05 8:49:39 PM
Reply
New FWIW this was the best I saw for our needs
The union did in fact get the exact right results. And was the clearest in what it was doing. Then someone observed that the main table was already an aggregate of results from several other tables, built just for running this one report. There are no other joins against it. The right way for us to do this is check and update (if necessary) the value of a flag in the main table every time we update an escalation.

All the other solutions suffer from the same problem. In order to get the correct data we'd have to order a specific way before grouping. But if that isn't the way we want the output ordered, we end up re-sorting in code anyway.
===

Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
     How to do without subqueries - (drewk) - (18) - April 21, 2005, 12:02:47 AM EDT
         SELECT DISTINCT t1.* FROM t1, t2 ...? - (FuManChu) - April 21, 2005, 01:26:10 AM EDT
         I don't understand your description - (ben_tilly) - (16) - April 21, 2005, 02:21:36 AM EDT
             Reply from Drew - (Meerkat) - (15) - April 21, 2005, 09:34:14 AM EDT
                 Re: Reply from Drew - (SpiceWare) - (6) - April 21, 2005, 09:59:22 AM EDT
                     DECODE is Oracle-specific. -NT - (ben_tilly) - (5) - April 21, 2005, 08:37:47 PM EDT
                         It is, but it is easy to clone - (tuberculosis) - April 22, 2005, 09:29:35 AM EDT
                         Are you sure? I checked MySQL before posting - (SpiceWare) - (3) - April 22, 2005, 09:54:09 AM EDT
                             They may have added it but it is not standard - (ben_tilly) - April 22, 2005, 11:16:56 AM EDT
                             Seems to be specific to MaxDB - (JayMehaffey) - (1) - April 22, 2005, 11:35:10 AM EDT
                                 That's rather confusing - (SpiceWare) - April 22, 2005, 12:14:55 PM EDT
                 One solution - (JayMehaffey) - (1) - April 21, 2005, 10:25:00 AM EDT
                     FWIW this was the best I saw for our needs - (drewk) - April 21, 2005, 08:49:39 PM EDT
                 Untested, googling for supported syntax - (ben_tilly) - April 21, 2005, 10:32:29 AM EDT
                 Re: Reply from Drew - (dws) - (4) - April 21, 2005, 08:17:36 PM EDT
                     You're ordering by the wrong thing - (ben_tilly) - (3) - April 21, 2005, 08:36:52 PM EDT
                         Where do you see me ordering by count(*) ? -NT - (dws) - (2) - April 22, 2005, 12:38:23 PM EDT
                             You weren't, clarification - (ben_tilly) - (1) - April 22, 2005, 02:08:36 PM EDT
                                 Re: You weren't, clarification - (dws) - April 22, 2005, 04:32:31 PM EDT

Gloat.
77 ms