4 ms·

With most databases (PostgreSQL is an exception), SELECT must select something, even if you only care about the record existing in general, and you're not inter

With most databases (PostgreSQL is an exception), SELECT must select something, even if you only care about the record existing in general, and you're not interested in reading anything from it. SELECT 1 means "select the first column", whatever it is. Specifying a column number also works with ORDER BY and other clauses. Of course any natural number works, as long as the record has at least that many fields.

pcthrowaway 3y ago
I'm pretty sure you still have to select something in postgres... or are you saying there's a different operator to do the same thing? > SELECT 1 means "select the first column" interesting.. in postgres this actually gives you a single-column value of 1, in an unnamed column... I haven't used other DBMSes much but expected similar behaviour
wruza 3y ago
SELECT 1 means "select the first column", whatever it is No, it selects a literal integer 1 in all SQL engines. You can, and do, select any expressions, not only column names. CategoryId, CategoryId + 1, 1, sqrt(CategoryId * ProductId), Column names alone are expressions too. SQL queries return not columns from table, but a result of relational operations with new columns specified by expressions. What a downvoted user itt cannot accept is that 1 is as meaningful as * or <colname> in general and is an idiom for “columns don’t matter here, only [the number of] rows”. It’s like [[1]].length vs [[id, …]].length. Saying “it’s not my data” is misunderstanding what that data is.
- magicalhippo 3y ago
  > You can, and do, select any expressions, not only column name I find this very useful when I need to insert some fixed data alongside some data from the database. I'll then use insert into TableX(Key, TotalWeight, Name, Category) select Key, sum(Weight), 'foo', 42 from TableY where Key = 1234 group by Key or something like that. Usually the source of the fixed data is in a spreadsheet, so I just use Excel to generate the SQL statements.
nice-try-fletch 3y ago
_SELECT 1 means "select the first column"_ In SQL Server at least, no, it literally means select the integer 1. In the ORDER BY clause, it does mean to order by ordinal position, but that's not a great thing to glorify, since ordinal position is not necessarily stable. I think other dialects like MySQL might allow GROUP BY 1, but that's not a great thing to glorify either.

pcthrowaway 3y ago

I'm pretty sure you still have to select something in postgres... or are you saying there's a different operator to do the same thing? > SELECT 1 means "select the first column" interesting.. in postgres this actually gives you a single-column value of 1, in an unnamed column... I haven't used other DBMSes much but expected similar behaviour

wruza 3y ago

SELECT 1 means "select the first column", whatever it is No, it selects a literal integer 1 in all SQL engines. You can, and do, select any expressions, not only column names. CategoryId, CategoryId + 1, 1, sqrt(CategoryId * ProductId), Column names alone are expressions too. SQL queries return not columns from table, but a result of relational operations with new columns specified by expressions. What a downvoted user itt cannot accept is that 1 is as meaningful as * or <colname> in general and is an idiom for “columns don’t matter here, only [the number of] rows”. It’s like [[1]].length vs [[id, …]].length. Saying “it’s not my data” is misunderstanding what that data is.

magicalhippo 3y ago
> You can, and do, select any expressions, not only column name I find this very useful when I need to insert some fixed data alongside some data from the database. I'll then use insert into TableX(Key, TotalWeight, Name, Category) select Key, sum(Weight), 'foo', 42 from TableY where Key = 1234 group by Key or something like that. Usually the source of the fixed data is in a spreadsheet, so I just use Excel to generate the SQL statements.

nice-try-fletch 3y ago

_SELECT 1 means "select the first column"_ In SQL Server at least, no, it literally means select the integer 1. In the ORDER BY clause, it does mean to order by ordinal position, but that's not a great thing to glorify, since ordinal position is not necessarily stable. I think other dialects like MySQL might allow GROUP BY 1, but that's not a great thing to glorify either.