Stack Overflow
Stack Overflow

Questions tagged [ansi-sql]

ANSI SQL is the American National Standards Institute adopted Structured Query Language standard, developed by ISO/IEC JTC 1. It is the base for the different SQL dialects used by different DBMS vendors.

ANSI SQL is the American National Standards Institute adopted Structured Query Language standard, developed by ISO/IEC JTC 1. It is the base for the different SQL dialects used by different DBMS vendors.

SQL/PSM (SQL/Persistent Stored Modules) is the ISO standard defining an extension of SQL with a procedural language for use in stored procedures.

In Oracle PL/SQL (for stored procedures) and SQL (for queries) are two completely different languages (and are internally executed by different engines). DB2, PostgreSQL and Firebird also make clear distinction between the query language (called SQL) and the procedural language (for which every vendor uses a different name).

Microsoft SQL Server doesn't make this distinction: only T-SQL is used which covers the query language and the procedural language.

355 questions
20
votes
5 answers

Someway to do `where booleanvalue=false` on both Sql Server and PostgreSQL?

I am attempting to make an application capable of running on both Sql Server and PostgreSQL. I can not seem to find a common expression that is basically select * from table where booleancol=false on SQL Server I must do(which is very confusing…
Earlz
  • 62,085
  • 98
  • 303
  • 499
19
votes
3 answers

Why do I need to explicitly specify all columns in a SQL "GROUP BY" clause - why not "GROUP BY *"?

This has always bothered me - why does the GROUP BY clause in a SQL statement require that I include all non-aggregate columns? These columns should be included by default - a kind of "GROUP BY *" - since I can't even run the query unless they're…
SqlRyan
  • 33,116
  • 33
  • 114
  • 199
18
votes
3 answers

ANSI SQL version of SELECT TOP 1

Is there an ANSI SQL compliant version of SQL SERVER's SELECT TOP n?
Andrew
  • 11,068
  • 17
  • 52
  • 62
13
votes
9 answers

There are a method to paging using ANSI SQL only?

I know: Firebird: FIRST and SKIP; MySQL: LIMIT; SQL Server: ROW_NUMBER(); Does someone knows a SQL ANSI way to perform result paging?
Click Ok
  • 8,700
  • 18
  • 70
  • 106
13
votes
2 answers

Postgres Next/Previous row SQL Query

I have the following table structures in a Postgres 9.1 database but the ideal solution should be DB agnostic if possible: Table: users |id|username| |1 |one | |2 |two | |3 |three | Table: items |id|userid|itemname|created | |1 |1 …
davec
  • 253
  • 1
  • 2
  • 10
13
votes
1 answer

column order in SELECT * statement - guaranteed?

I am using an ORM (sqlalchemy) to fetch data from a PG database. I want to avoid specifying all the table column names in my hand crafted SQL statements*. My assumption so far is that the returned columns are in the order of the DDL statements used…
Homunculus Reticulli
  • 65,167
  • 81
  • 216
  • 341
12
votes
2 answers

BigQuery Standard SQL: how to return the first value of array?

Small working example SELECT SPLIT("hello::hej::hallo::hoi", "::") returns an array [hello, hej, hallo, hoi] where I want to select the first element i.e. hello. BG Standard provides no FIRST, instead FIRST_VALUE(..) OVER() which I cannot get…
hhh
  • 50,788
  • 62
  • 179
  • 282
12
votes
4 answers

Standard SQL alternative to Oracle DECODE

Is there an ANSI SQL equivalent to Oracle's DECODE function? Oracle's decode function is the IF-THEN-ELSE construct in SQL.
JavaRocky
  • 19,203
  • 31
  • 89
  • 110
12
votes
3 answers

SQL: do we need ANY/SOME and ALL keywords?

I'm using SQL (SQL Server, PostgreSQL) over 10 years and still I'm never used ANY/SOME and ALL keywords in my production code. All situation I've encountered I could get away with IN, MAX, MIN, EXISTS, and I think it's more readable. For…
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
10
votes
3 answers

Is Coalesce a function of ANSI SQL

Is the COALESCE a function of the ANSI SQL especification? Is it supported by the major relational databases?
Rodrigo
  • 2,313
  • 2
  • 15
  • 23
10
votes
3 answers

Implement Rank without using analytic function

I am wondering if there is a method to implement SQL analytic functions without using the inbuilt functions. SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rownum, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY…
Teja
  • 13,214
  • 36
  • 93
  • 155
9
votes
5 answers

AutoIncrement fields on databases without autoincrement field

In MS Sql Server is easy create autoincrement fields. In my systems I stopped to use autoincrement fields for primary keys, and now I use Guid's. It was awesome, I've got a lot of advantages with that change. But in another non-primary key fields, I…
Click Ok
  • 8,700
  • 18
  • 70
  • 106
9
votes
4 answers

ORDER BY in both queries of the EXCEPT clause

I am loking to implement a paging query in ANSI SQL. I tried the following query in SQL Server, but it did not allow it: select top 20 * from MyTable order by id except select top 10 * from MyTable order by id But the following works fine: select…
Thunder
  • 10,366
  • 25
  • 84
  • 114
8
votes
3 answers

Does the order of tables in a join matter, when LEFT (outer) joins are used?

I would like to confirm that the SQL query SELECT .... FROM apples, oranges LEFT JOIN kiwis ON kiwis.orange_id = oranges.id, bananas WHERE .... is exactly equivalent to other permutations in the FROM subclause, like SELECT…
Radio Yerevan
  • 83
  • 1
  • 1
  • 3
8
votes
1 answer

Count unique ids in a rolling time frame

I have a simple table as bellow with lots of IDs and dates. ID Date 10R46 2014-11-23 10R46 2016-04-11 100R9 2016-12-21 10R91 2013-05-03 ... ... I want to formulate a query which counts the unique IDs for a rolling time frame…
ErikK
  • 83
  • 1
  • 3
1
2
3
…
23 24