Discussion :

[khadir]

Bonjour ,

je cherche l'equvalent de la clause LIMIT(debu,nombre) qui est disponible ds mysql pour selectionner le 'nombre' des lignes des resultats a partir de la ligne 'debut' ?

je notte que pour la clause TOP(nombre) dans Transact Sql ,ya pas la notion debut...

Merci d'avance

[rudib]

Bonjour,

Tu peux t'en sortir avec une fonction de fenêtrage : RANK() OVER (PARTITION)

Cherche la fonction RANK() dans l'aide en ligne.

[khadir]

Merci sur ta reponse..

tu peut detailler en peu... un exemple .. /?

[rudib]

En fait, pour ton LIMIT, ROW_NUMBER(), une autre fonction de fenêtrage, convient mieux.
Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT TOP 10 * FROM (SELECT
   ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
   *
FROM
   dbo.Customers) c
WHERE
   RowNumber > 10

Ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT * FROM (SELECT
   ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
   *
FROM
   dbo.Customers) c
WHERE
   RowNumber BETWEEN 10 AND 20

[khadir]

Bonjour
voici une soulition :

select * from
(select top(2) * from
(select top(13+2) * from user order by idASC) as tbl1
order by id desc) as tbl2 order by idUser ASC
<========>

select * from user LIMIT(2,13)

[mikaeru]

Merci à tous,
finallement j'ai repris l'exemple de Khadir.
SQLPRO, si tu pourrais expliqué ton idée, cela serai interessant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select  Projectname,Filename,TMPname,SPname,Hour_start,Hour_end,enable,isnull (Comment,'') Comment,
-- give the received time for 2 last files
(select top 1 received from db_inject_hist dih where  DID.DID_id=DIH.DIH_ID_DID order by received desc ) Last_file,
(select top 1 received from (select top 2 received from db_inject_hist dih where  DID.DID_id=DIH.DIH_ID_DID order by received desc ) t1 order by received) prev_file,
-- give the number of rows in the temp table for 2 last files
(select top 1 rows from db_inject_hist dih where  DID.DID_id=DIH.DIH_ID_DID order by received desc ) Last_rows_tmp,
(select top 1 rows from (select top 2 received,rows from db_inject_hist dih where  DID.DID_id=DIH.DIH_ID_DID order by received desc ) t1 order by received) prev_rows_tmp,
-- give the number of rows in the temp table for 2 last files
(select top 1 status from db_inject_hist dih where  DID.DID_id=DIH.DIH_ID_DID order by received desc ) Last_status,
(select top 1 status from (select top 2 received,status from db_inject_hist dih where  DID.DID_id=DIH.DIH_ID_DID order by received desc ) t1 order by received) prev_status
 
from DB_inject_data did