use Perl

• DISTINCT or ORDER BY ? (Score:1)

  by n1vux (1492)

  A wise man said any SQL query without an ORDER BY clause is a stealth bug waiting to emerge when the implicit order dependency explodes into view.

  In the customer-visit itinerary, ORDER BY may actually be a better solution than DISTINCT -- the repetition value in the BAG will be needed to help plan how long to lay over in each city, as it measures how many visits are required there. GROUP BY (City) COUNT(Customer) might be better, and would get you the equivalent of DISTINCT.

  --
  Bill
  # I had a sig when sigs were cool
  use Sig;
  • Re:DISTINCT or ORDER BY ? (Score:2)

    by autarch (914)

    If you need to know counts that's what "GROUP BY" is for, so we can do:

    SELECT city, COUNT(customer_id) as customer_count
      FROM Customer
    GROUP BY city
    ORDER BY city

    This will have no dupes and gives you the same numerical information as a dupe-containing result, but in a nice sane way, with no need to count dupes programatically in the program that executes that query.
    
  • Re:DISTINCT or ORDER BY ? (Score:2)

    by Ovid (2709)

    You've come up with a rather artificial reason why one might, in this case, want a bag instead of a set, but your example is arbitrary. Who knows why Alice is in a particular city? Maybe she has some weird contractual agreement that she needs to at least be in those cities once a year? The point is the same: if I ask for information, why should I want my query, by default, to repeat itself rather than just give me the information I ask for?

    Don't focus on the specifics of this particular problem becaus
    • DISTINCTly DisORDERed (Score:1)

      by n1vux (1492)

      I think we're in violent agreement that naive SQL is bad, and we're just arguing over which form of naivety is worst!

      This is not just a artificial quibble on the artificial example. I gave a different, more general rule that explains why the artificial example is wrong at an even deeper level. I claim that in general both general rules need to be addressed together.

      I agree that SQL having been designed abstractly by mathematicians (with whom I happily self-identify) so that it encourages thinking SETs y

      --
      Bill
      # I had a sig when sigs were cool
      use Sig;