[SQL] mysql limit alternatief

[ggvw]

Heb eindelijk voor elkaar hoe je een soort mysql limit kan nabootsen die in ieder geval werkt in postgresql (7.4) , mysql (4.1) en mssql (server 2000).

Ik post het maar even, misschien dat iemand anders er nog iets aan heeft. Op internet kan je namelijk wel veel alternatieven voor 'limit' vinden, maar die zijn vaak te database specifiek.

Onderstaand voorbeeld haalt de eerste 25 records op uit een tabel genaamd 'test' met een auto incrementing 'id' veld en een 'veld1' en een 'veld2' (varchar) .

code:

select 
    t1.id,
    t1.veld1,
    t1.veld2
from test t1 
inner join test t2 on t1.id=t2.id 
where 
  (select count(t2.id) from test t2 where (t2.id < t1.id)) between 0 and 24 
order by t1.veld1 desc

Handig voor het maken van database onafhankelijke applicaties. Al heb ik de performance nog niet getest van deze constructie..

[momania]

Ik denk toch dat je er beter voor kan kiezen om adv een parameter/setting ergens de databaste type te achterhalen in je code en zo gewoon de database specifieke limt te gebruiken.

Qua performance is je huidige query niet echt super denk ik en of het ook bij complexere queries gaat werken is nog maar de vraag.

[NMe]

Je kan met TOP iets doen... Met TOP selecteer je de eerste X records uit een tabel. Stel je wil vanaf het 60e record 20 records zien, en er zitten 100 records in je tabel, dan select je de eerste 40 in descending order, en daar weer de eerste 20 van in ascending order. Die 80 selecteer je dus in een subquery van de query waar je de eerste 20 selecteert.

Databaseonafhankelijk is dat echter niet, want het zal niet werken in MySQL.

[ggvw]

Was net zo blij met m'n methode want volgens mij is mijn oplossing er een die aan de ansi standaard voldoet, al weet ik dit niet 100% zeker.

[ Voor 10% gewijzigd door ggvw op 09-03-2005 22:07 ]

[NMe]

ggvw schreef op woensdag 09 maart 2005 @ 22:07:
Was net zo blij met m'n methode want volgens mij is mijn oplossing er een die aan de ansi standaard voldoet, al weet ik dit niet 100% zeker.

Jouw oplossing werkt ook niet in de meestgebruikte versies van MySQL, aangezien je een subquery gebruikt. In dat opzicht kun je net zo goed 'mijn' oplossing gebruiken, die iets lichter is. Verder voldoet je query inderdaad aan de standaard voor zover ik kan zien, maar niet elk DBMS implementeert die standaard perfect. Er zijn nou eenmaal dialectverschillen.

[ggvw]

Jouw oplossing is de methode die ik nu gebruik. Ik zocht namelijk naar iets algemeners omdat ik geen zin had al m'n applicaties aan te passen voor database xyz wanneer de klant opeens mocht besluiten om die te gaan gebruiken (die kans bestaat, helaas). Wanneer de nieuwe database enigszins de standaard ondersteund dan is er een grotere kans dat dit blijft werken.

[RSpliet]

ik weet dat MySQL, PGSQL en SQLite iig allemaal het volgende liever vinden dan de wat MySQL aanleert 'Limit 0,20':

SQL:

SELECT * FROM pie LIMIT 20 OFFSET 0

... ik weet niet wat MSSQL daarvan vindt... Mocht die het niks vinden, dan is het denk ik makkelijker in je database-klasse een soort rewrite-achtige functie te schrijven voor wat MSSQL vindt. Dus een functie die de Limit in een query vervangt door iets dat die database-server wel liev vindt - of - een aparte functie die in het geval van MySQL returnt "Limit 0,20", in het geval van PGSQL "Limit 20 offset 0" en bij MSSQL wat MSSQL dus slikt... bijvoorbeeld.

[ Voor 67% gewijzigd door RSpliet op 09-03-2005 22:35 ]

[ggvw]

Seven of Nine schreef op woensdag 09 maart 2005 @ 22:30:
ik weet dat MySQL, PGSQL en SQLite iig allemaal het volgende liever vinden dan de standaard Limit 0,20:

SQL:

SELECT * FROM pie LIMIT 20 OFFSET 0

... ik weet niet wat MSSQL daarvan vindt...

MSSQL gaat zeuren over limit..

Hmm, ik zal eens kijken of m'n oplossing ook in SQLite werkt.

makkelijker in je database-klasse een soort rewrite-achtige functie te schrijven

Zo doe ik het nu idd, maar dat komt vaak toch neer op het herhalen van je query met een if block die het type database controleert, zoals -nme- ook al zei.

[ Voor 25% gewijzigd door ggvw op 09-03-2005 22:41 ]

[Cuball]

ggvw schreef op woensdag 09 maart 2005 @ 21:47:

code:

select 
    t1.id,
    t1.veld1,
    t1.veld2
from test t1 
inner join test t2 on t1.id=t2.id 
where 
  (select count(t2.id) from test t2 where (t2.id < t1.id)) between 0 and 24 
order by t1.veld1 desc

code:

select t1.id, t1.veld1, t1.veld2 
from test t1
where 24 < (select count(*) from test t2 where t1.id < t2.id) 
order bij t1.veld1 desc

probleem hierbij is wel dat je kans hebt als je 2 dezelfde waarden hebt dat die beide in je resultaat komen, je kan dus meer dan 24 rijen terug krijgen


ik gebruik altijd de FETCH first x ROWS ONLY maar die is volgens mij enkel DB2 :-)

[_Thanatos_]

In MSSQL 2005 (Yukon) ga je ook zoiets kunnen doen:

SQL:

WITH OrderedTable AS (
   SELECT Veld1, Veld2, ROW_NUMBER() OVER (ORDER BY Veld3) AS rijtje
   FROM Tabelletje
)
SELECT * FROM OrderedTable WHERE rijtje BETWEEN 50 AND 60

[ggvw]

Cuball schreef op woensdag 09 maart 2005 @ 23:38:
[...]
probleem hierbij is wel dat je kans hebt als je 2 dezelfde waarden hebt dat die beide in je resultaat komen, je kan dus meer dan 24 rijen terug krijgen

Dat klopt, m'n alternatief voor limit werkt dan ook alleen met een auto incrementing primary key als 'id' veld. Dat is hier bijna altijd wel het geval.

[curry684]

In MSSQL2k is dit gewoon de correcte variant om 25 tot 50 te krijgen:

SQL:

SELECT TOP 25 *
FROM messages
WHERE msg_id NOT IN 
    (SELECT TOP 25 msg_id FROM messages ORDER BY date DESC)
ORDER BY date DESC;

De query optimizer maakt hier moeiteloos een hele prettige schone execution van die zeer performant is

[NMe]

curry684 schreef op donderdag 10 maart 2005 @ 14:17:
In MSSQL2k is dit gewoon de correcte variant om 25 tot 50 te krijgen:

SQL:

SELECT TOP 25 *
FROM messages
WHERE msg_id NOT IN 
    (SELECT TOP 25 msg_id FROM messages ORDER BY date DESC)
ORDER BY date DESC;

De query optimizer maakt hier moeiteloos een hele prettige schone execution van die zeer performant is

Briljant, en verbazend simpel in vergelijking met mijn oplossing hierboven.

Dit had ik een jaar geleden moeten weten.

[Verwijderd]

ggvw schreef op donderdag 10 maart 2005 @ 13:43:
[...]

Dat klopt, m'n alternatief voor limit werkt dan ook alleen met een auto incrementing primary key als 'id' veld. Dat is hier bijna altijd wel het geval.

Volgens mij gaat jou query ook de mist in als er records gedelete zijn. Dan retourneert hij weer te weinig records.

Ik zie trouwens het nut niet echt van dit soort functies. Zelfs deze simpele query

SQL:

Select Naam from table Order By Naam

retourneert al andere data als het op Oracle of MS-SQL gedraaid wordt. Bij MS komen de null waarden eerst, bij Oracle het laatst. Als dit soort basis queries al aangepast moeten worden, dan mis ik het nut van methoden om goed werkende functies te omzeilen.

Een aardige pagina over de verschillende SQL implementaties en hoe alles in de verschillende implementaties werkt is trouwens http://troels.arvin.dk/db/rdbms/

[ Voor 27% gewijzigd door Verwijderd op 11-03-2005 10:09 ]

[koli-man]

ggvw schreef op woensdag 09 maart 2005 @ 22:32:
[...]


MSSQL gaat zeuren over limit..

Hmm, ik zal eens kijken of m'n oplossing ook in SQLite werkt.

[...]

Zo doe ik het nu idd, maar dat komt vaak toch neer op het herhalen van je query met een if block die het type database controleert, zoals -nme- ook al zei.

Sybase vind LIMIT ook niet lief.

[Verwijderd]

curry684 schreef op donderdag 10 maart 2005 @ 14:17:
In MSSQL2k is dit gewoon de correcte variant om 25 tot 50 te krijgen:

SQL:

SELECT TOP 25 *
FROM messages
WHERE msg_id NOT IN 
    (SELECT TOP 25 msg_id FROM messages ORDER BY date DESC)
ORDER BY date DESC;

De query optimizer maakt hier moeiteloos een hele prettige schone execution van die zeer performant is

Zeer performant... Totdat je where clause bv. (select top 20000 ...) gaat bevatten. En helaas komt dat in de praktijk best vaak voor.

[Verwijderd]

Verwijderd schreef op vrijdag 11 maart 2005 @ 11:50:
[...]


Zeer performant... Totdat je where clause bv. (select top 20000 ...) gaat bevatten. En helaas komt dat in de praktijk best vaak voor.

Mijn favoriet is toch dit:

code:

SELECT * FROM (
  SELECT TOP n * FROM (
    SELECT TOP z columns      
    FROM tablename
    ORDER BY key ASC
  ) AS FOO ORDER BY key DESC 
) AS BAR ORDER BY key ASC

Niet eens zelf bedacht
Maar het grote voordeel is dat de subqueries onafhankelijk zijn. Je werkt dus op een steeds kleiner wordende dataset. Eerst Z=N+SKIP records, dan N en daarna keer je van die N records de sorteer volgorde om. Maar als iemand iets effectievers weet Gecorreleerde queries zijn in ieder geval niet mijn favoriet, maar ik denk dat dat wel voor meer mensen geldt.

[Verwijderd]

Verwijderd schreef op zaterdag 12 maart 2005 @ 03:17:
[...]


Mijn favoriet is toch dit:

code:

SELECT * FROM (
  SELECT TOP n * FROM (
    SELECT TOP z columns      
    FROM tablename
    ORDER BY key ASC
  ) AS FOO ORDER BY key DESC 
) AS BAR ORDER BY key ASC

Niet eens zelf bedacht
Maar het grote voordeel is dat de subqueries onafhankelijk zijn. Je werkt dus op een steeds kleiner wordende dataset. Eerst Z=N+SKIP records, dan N en daarna keer je van die N records de sorteer volgorde om. Maar als iemand iets effectievers weet Gecorreleerde queries zijn in ieder geval niet mijn favoriet, maar ik denk dat dat wel voor meer mensen geldt.

Ik ga deze maar eens proberen, zit er wel erg netjes uit () en lijkt op het 1e gezicht precies wat wil zoek. Jammer dat sql niet gewoon de functie LIMIT heeft opgenomen, dat scheelt nogal wat programmeerwerk.

Verder zou ik persoonlijk nooit SELECT * FROM doen en zeker niet in dit voorbeeld, maar altijd netjes SELECT ID, VELD FROM doen. Dit komt de performance weer ten goede.