Ordenar filas
Asunto: Pregunta de SQL
('select a.codcliente as Cod_Cliente, a.nombre as Nombre,');
('a.direccion as Dirección, a.telefonocasa as Teléfono_Casa,');
(' a.telefonotrabajo as Teléfono_Trabajo, a.zona as Zona, a.producto, a.clienteinstitucion as cliente, a.corte from marketingtabla as a, temporal as b');
('where a.zona=b.zonatemporal and a.producto=b.producto and a.clienteinstitucion=b.cliente and a.corte=b.corte');
('order by a.nombre ASC' );
Tengo esta consulta de dos tablas relacionadas y quisiera saber como hacer que los registros que se generen esten numerados desde el uno en adelante hasta el el ultimo registro generado. Si pudieras contestarmelo se lo agradeceria en el alma.
Gracias anticipadas,
José Miguel Méndez
('select a.codcliente as Cod_Cliente, a.nombre as Nombre,');
('a.direccion as Dirección, a.telefonocasa as Teléfono_Casa,');
(' a.telefonotrabajo as Teléfono_Trabajo, a.zona as Zona, a.producto, a.clienteinstitucion as cliente, a.corte from marketingtabla as a, temporal as b');
('where a.zona=b.zonatemporal and a.producto=b.producto and a.clienteinstitucion=b.cliente and a.corte=b.corte');
('order by a.nombre ASC' );
Tengo esta consulta de dos tablas relacionadas y quisiera saber como hacer que los registros que se generen esten numerados desde el uno en adelante hasta el el ultimo registro generado. Si pudieras contestarmelo se lo agradeceria en el alma.
Gracias anticipadas,
José Miguel Méndez
1 respuesta
Respuesta de athrarn
1
select a.codcliente as Cod_Cliente, a.nombre as Nombre,
a.direccion as Dirección, a.telefonocasa as Teléfono_Casa,
a.telefonotrabajo as Teléfono_Trabajo, a.zona as Zona, a.producto,
a.clienteinstitucion as cliente, a.corte from marketingtabla as a, temporal as b,rouwnum NumColumna
where a.zona=b.zonatemporal and a.producto=b.producto and a.clienteinstitucion=b.cliente and a.corte=b.corte
order by a.nombre ASC
a.direccion as Dirección, a.telefonocasa as Teléfono_Casa,
a.telefonotrabajo as Teléfono_Trabajo, a.zona as Zona, a.producto,
a.clienteinstitucion as cliente, a.corte from marketingtabla as a, temporal as b,rouwnum NumColumna
where a.zona=b.zonatemporal and a.producto=b.producto and a.clienteinstitucion=b.cliente and a.corte=b.corte
order by a.nombre ASC
La sentencia que estoy diseñando es para una base de datos en ACCESS y no funciona esta sentencia para la generacion de un orden numerico de los registros generados. El programa es en Delphi y desde un formulario realizo la siguente consulta. Esta sentencia Rouwnum NumColumna no la encuentro en la ayuda de ACCESS.
Lo busque en internet, dado que no sabia como era esa sentencia en Access. Parece ser TOP.
La consulta en cuestion parece ser :
SELECT TOP 25 * FROM tbl
Para que te devuelva los 25 primeros.
La documentacion la saque de aqui :
http://users.starpower.net/rjhalljr/Serve/AccSql/sql.html
Por si no la ves, la parte que nos concierne es esta :
Returning Groups Of Rows In Sequence
For web pages, you often want to return rows in groups of some number. For example, you may want to insert the first twenty five rows into the first page, the next twenty five into the second page, and so on. If you can't or don't want to do this in the client applications, the following SQL statements will do the job. This approach assumes that the rows in the table are ordered on one column (fldOrder).
SELECT TOP 25 * FROM tbl;
Now it gets a little tricky. To get the next group of twenty five rows, start by selecting the first fifty.
SELECT TOP 50 * FROM tbl;
Now use the query above as a subquery in a query that pulls the bottom twenty five from the selected fifty.
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC;
That gets the correct twenty five rows, but in the wrong order. The last step is to put them back in the proper order.
SELECT *
FROM (
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC
)
ORDER BY fldOrder;
The SQL statement above will run, but when you close the query and then reopen it in SQL View, you'll see that Access has changed the statement. Don't worry about it. Either version works.
La consulta en cuestion parece ser :
SELECT TOP 25 * FROM tbl
Para que te devuelva los 25 primeros.
La documentacion la saque de aqui :
http://users.starpower.net/rjhalljr/Serve/AccSql/sql.html
Por si no la ves, la parte que nos concierne es esta :
Returning Groups Of Rows In Sequence
For web pages, you often want to return rows in groups of some number. For example, you may want to insert the first twenty five rows into the first page, the next twenty five into the second page, and so on. If you can't or don't want to do this in the client applications, the following SQL statements will do the job. This approach assumes that the rows in the table are ordered on one column (fldOrder).
SELECT TOP 25 * FROM tbl;
Now it gets a little tricky. To get the next group of twenty five rows, start by selecting the first fifty.
SELECT TOP 50 * FROM tbl;
Now use the query above as a subquery in a query that pulls the bottom twenty five from the selected fifty.
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC;
That gets the correct twenty five rows, but in the wrong order. The last step is to put them back in the proper order.
SELECT *
FROM (
SELECT TOP 25 *
FROM (
SELECT TOP 50 * FROM tbl
)
ORDER BY fldOrder DESC
)
ORDER BY fldOrder;
The SQL statement above will run, but when you close the query and then reopen it in SQL View, you'll see that Access has changed the statement. Don't worry about it. Either version works.
