Avatar

No postgresql. (Features)

by Auge ⌂ @, Wednesday, May 15, 2019, 10:49 (127 days ago) @ Micha

Hello

If we restrict our self to the standard SQL, each DBMS is compatible to each other, isn't it?

I would not expect it to work.

Example:

Task: return datasets 26 to 50 for a paginated listing

MySQL (non standard, we use it):

SELECT * 
FROM TABLE
LIMIT 26, 25

PostgreSQL (non standard):

SELECT *
FROM TABLE
LIMIT 25 OFFSET 26;

MS SQL (>= 2012, don't know if this is standard):

SELECT *
FROM TABLE OFFSET 26 ROWS FETCH NEXT 25 ROWS ONLY

MS SQL (<2012, seems to be an ugly workaround):

 
-- taken from https://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server
WITH o AS
(
    SELECT TOP ((26 - 1) + 25)
      RowNum = ROW_NUMBER() OVER (ORDER BY ID ASC)
    FROM TABLE
)
SELECT 
    RowNum
    /* , other columns */
FROM
    o
WHERE
    RowNum >= 26
ORDER BY
    RowNum;

This is only one example for a function, we use with different solutions in different implementations. As stated: I would not expect it to work.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!


Complete thread:

 RSS Feed of thread

powered by my little forum