Does SQL Server support "DISTINCT ON"?

Post #258,103 by drewk 6/8/06 8:42:29 AM 6/8/06 8:55:49 AM Reply	Does SQL Server support "DISTINCT ON"? It's an extension I used a lot in PostgreSQL. You'd get: SELECT DISTINCT ON (d.drugid) \nFROM\n drug d\n LEFT OUTER JOIN formulary f ON (f.drugid = d.drugid)\nORDER BY\n f.formularyid [EDIT] Nope, scratch that. I checked the PostgreSQL docs and it appears the behavior of `DISTINCT ON` one field when ordering by a different field is undefined and pretty much broken. The better solution would be: SELECT d., f.*, MIN(f.formularyid) AS f_unique\nFROM\n drug d\n LEFT OUTER JOIN formulary f ON (f.drugid = d.drugid)\nGROUP BY\n d.drugid Without having it installed here, I'm not sure what the `MIN()` will do with null rows returned by the `OUTER JOIN`. Assuming that returns the "correct" thing, this seems more readable than Ben's version. I suspect his may perform better on large tables though. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com] Edited by drewk June 8, 2006, 08:55:49 AM EDT Expand All History
Post #258,137 by ChrisR 6/8/06 10:56:50 AM Reply	That is really what I need! Unfortunately it doesn't appear to be available for SS. But being able to restrict the DISTINCT to a particular column(s) would've been a nice solution. Of course, that leaves the problem of which row to keep and which to throw away.
Post #258,162 by ben_tilly 6/8/06 1:12:25 PM Reply	The more readable version is invalid (at least in Oracle) Oracle wants every field in the SELECT that is not a GROUP BY to be an aggregate. So you'd need all of f.* to be in the GROUP BY, at which point your min(formularyid) would accomplish nothing (since you've grouped by formularyid). That is why I needed two inner levels. One to ind the minimum formularyid for the drugid, and the other to pull out all of the formulary information for that ID. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #258,168 by drewk 6/8/06 1:49:01 PM Reply	Hmm, both Postgres and MySQL support the same non-standard? I'm fairly certain that in both of them when you SELECT * from a table, MIN() on one value, GROUP BY another value, you get the entire row that corresponds to the record that contained the MIN. I'm so sure I used to use this behavior, and got the expected results, that I think they both do it. Or I may have lost my mind since then. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #258,174 by folkert 6/8/06 2:04:49 PM Reply	Can you please help me find mine? Since you only lost your recently, its more likely to be found. -- [link\|mailto:greg@gregfolkert.net\|greg], [link\|http://www.iwethey.org/ed_curry\|REMEMBER ED CURRY!] @ *i_wethey* Freedom is not FREE. Yeah, but 10s of Trillions of US Dollars? SELECT * FROM scog WHERE ethics > 0; 0 rows returned.
Post #258,177 by ben_tilly 6/8/06 2:14:38 PM Reply	That is a MySQL shortcut And it doesn't work like you think it does. First of all it doesn't work in any version of Oracle through Oracle 10. I just tried it. If you wish to try it in other databases, be my guest. I'd be interested in who elsedoes that. But from [link\|http://lists.evolt.org/archive/Week-of-Mon-20030908/148130.html\|comments] that I saw while browsing, missing fields in the group by is something that MySQL allows and Postgres does not. In [link\|http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html\|the MySQL documentation] the extension is explained and they admit that it is not standard SQL. They also document the fact that MySQL is free to return any value in the group, so while you might sometimes get the row corresponding to the min, they tell you that you can't rely on that behaviour. Note that the MySQL documentation for how to be portable is exactly backwards, at least as it comes to dealing with Oracle. Oracle allows expressions in the group by but won't recognize an alias there. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #258,189 by drewk 6/8/06 2:42:46 PM Reply	Not surprising I haven't used Postgres in anger for a couple of years. I'm sure there's a lot of MySQL-isms that I incorrectly attribute to Postgres. As for getting the wrong values back when using this technique in MySQL: either I got very lucky very often, or I used to know a way to make sure you got the right thing back, which I have since forgotten. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #258,191 by ben_tilly 6/8/06 2:50:14 PM Reply	Or you didn't double-check enough Logic errors are the hardest kind of bugs to find. Depending on the implementation, if you were trying it in an example like the above on a table that had been filled in sequentially, it might frequently pick the minimum row. So it would look like it was doing something, even when it wasn't. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #258,205 by drewk 6/8/06 4:17:32 PM Reply	Also possible Though it was long enough ago that I can't even remember a representative example. I'll chalk that up as "something I may have gotten away with". It wouldn't be the only thing. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #258,256 by static 6/8/06 8:48:27 PM Reply	I've encountered that problem. I didn't have the patience to figure out a solution in SQL, but could do it another way - one of my favourite solutions is temporary tables, but they bring their own problems. We had Oracle on the cards a few months ago and so I went [link\|http://troels.arvin.dk/db/rdbms/\|researching compatibility]. I was surprised that Oracle had so many gotchas. But then there's a certain arrogance from Oracle about The Way To Do Databases, isn't there? :-) Wade. "Insert crowbar. Apply force."
Post #258,263 by ben_tilly 6/8/06 9:18:47 PM Reply	Justifiable arrogance in many cases Much of what I do with analytic queries you simply Would Not Want to try with MySQL. Ditto if you are paranoid about your data. However there is no question that MySQL wins hands down on cost and ease of installation. Its raw performance for simple things ain't shabby either. (On complex queries, Oracle wins again.) If you want a more detailed comparison, Barry is the one to talk to. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #258,265 by static 6/8/06 9:35:59 PM Reply	Oh I have little doubt about that. However, I neglected to mention I was limiting my comment to their brand of SQL. :-) I'm beginning to think we're coming up against some of MySQL's more subtle and complex limits. I wish I could engineer to time to try my app in PostGres... Oracle is not an option for us, financially. Wade. "Insert crowbar. Apply force."
Post #258,268 by ben_tilly 6/8/06 10:03:53 PM Reply	Oracle is picky but... its brand of SQL is more flexible once you get used to it. That is, there are not so many shortcuts. But you can do more with it. (Though MySQL is one by one removing its limitations. There is no question that Oracle is not the future of databases.) Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)

Welcome to IWETHEY!