10 ms·
What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a
by hnthrowaway0315 5y ago
What I really need is something like:
SELECT -col1, -col14 FROM table LIMIT 50;
Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).
- dolmen 5y ago> SELECT -col1, -col14 FROM table LIMIT 50; This is already valid SQL. Example: SELECT -col1, -col4 FROM (SELECT 1 AS col1, 2 AS col4) AS tbl; Do you think seriously that a new meaning could ever be attached to that syntax?
- hnthrowaway0315 5y agoI just say something similar, doesn't have to be the exact form.
- stingraycharles 5y agoSimple, effective, and really useful in a lot of situations. I like it!
- nvartolomei 5y agoSome have support for this. - BQ https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax https://cloud.google.com/bigquery/docs/reference/standard-sq... - CH https://clickhouse.com/docs/en/sql-reference/statements/select/#except-modifier https://clickhouse.com/docs/en/sql-reference/statements/sele...
- hnthrowaway0315 5y agoThanks, these really look neat. Unfortunately we only use Vertica :/
- piaste 5y agoSimilarly, I'd love some form of GROUP BY every column except for <these> It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.
- Svip 5y agoYou know, you can just write GROUP BY x, y, ... SELECT t.i+1, count(*) FROM table t GROUP BY 1 1 in this context means the first selected item (i.e. t.i+1). I know this works in PostgreSQL.
- edoceo 5y agoI use this. It gets messy after like 10 columns. I have a trick in my ORM that copies the non-aggrigate columns into the statement. Maybe your ORM has it?
- dragonwriter 5y agoWhat I really want is a special phrase MINIMAL GROUPING such that GROUP BY MINIMAL GROUPING includes exactly the items that it would be an error not to include in the GROUP BY.
- icambron 5y agoI wish it just had a "group by all the stuff I selected without aggregation" shortcut
- xyzzy_plugh 5y agoIsn't this just SELECT DISTINCT ...?
- orthoxerox 5y agoNo. What icambron and I would like is something that can get rid of this group by: select division_name, branch_name, dealer_id, dealer_name, quarter, month, sum(total_paid) from divsions, branches, dealers, transactions where ..... --buncha joins group by division_name, branch_name, dealer_id, dealer_name, quarter, month If I didn't want to group the result by division_name, branch_name, dealer_id, dealer_name, quarter and month, why would I put them in the select clause?
- 5y ago
- c06n 5y agoR's data.table has that. dt[1:50, -c('col1', 'col14')]
- jdunstan 5y agoThat's very close to SELECT *, which has it's own dangers. I agree that it would be nice for exploration and testing, but probably should not be used in production.
- ipaddr 5y agoWhat dangers lay in select *...? Too much data?
- wiredfool 5y agoMinimal if you refer to columns by name. Risky if you rely on column ordering. Can be too much data and a performance issue if you have large columns that you’re not using. (Though, it’s not much different if you’re using an orm that loads the whole object anyway), like Django or sqlalchemy. I’ve done it for many years (using named columns/ dictionaries as the result set) and its never been an issue.
- hnthrowaway0315 5y agoThe problem I found out is that SELECT * LIMIT 10 is guaranteed to be needed so it's actually much more important than whatever is in production (you have to explore a lot before writing the production scripts). My idea is that exploration should be as easy as possible. Things such as SELECT * EXCEPT col1, col2 are really a PIA to write and can build up frustration level really quickly. Certain IDEs such as Datagrip ease the process by providing "macros" but they are not enough. Another thing is to generate useful boilerplates such as SELECT col1 FROM table GROUP BY col1 ORDER BY col1 to explore all unique values of col1.
- bobbyi 5y agoIf you have specific columns that you frequently want to ignore (which, I think, is the common case of this), you could define a view that selects all the columns except those and do your queries against that view.
- Eremotherium 5y agoOr create a function that returns a view of a table minus named columns in situ if that's really a common case for you. Just as an idea.
- fendy3002 5y agoIirc in some older db it won't be able to use indexes this way. Don't know if still applied nowadays.
- blondin 5y agoi would rather it written as SELECT * BUT or SELECT * EXCEPT or even SELECT ALL BUT. SQL has always been that language that is easy to read. even when you don't understand what the queries are doing. adding a cryptic syntax like "-column" would make it less readable.
- colinmhayes 5y agoEXCEPT is already a sql keyword
- polygotdomain 5y agoWhat about SELECT * WITHOUT {columns_you_do_not_want} FROM... EXCEPT is already a keyword and has is used for set-based operations, so I don't think it's good to overload it.
- edgyquant 5y agoI like this fwiw
- recursive 5y agoThe given syntax is obviously a no-go. It's already unary minus.
- dspillett 5y agoThere are often occupancies where I'd like this in ad-hoc queries, but it would be something to avoid and discourage in production code for the same reasons “SELECT *” is discouraged (because it is “SELECT *” with a bit of sugar added)