2 SubConsultas con Maximo y Minimo

AGAG4 · #1 02-09-2004

Las Consultas las hago en FireBird 1.5 con IbExpert.
Tengo el siguiente problema, lo que quiero es mostrar en una sola consulta el Valor Máximo y Mínimo con sus respectivos campos cada uno, Tengo la siguiente Tabla:
TOPE ! TOPELISTA ! TOPEMAY
-------------------------------
11.50 ! 0.89 ! 0.74
11.66 ! 0.99 ! 0.78
11.80 ! 0.89 ! 0.78
11.84 ! 0.79 ! 0.68

Quiero el valor Máximo y Mínimo del campo "TOPE " que a la vez quiero que me visualice su respectivo valor de TOPELISTA y TOPEMAY como por Ejemplo:

El Maximo sería = 11.84 ! 0.79 ! 0.68
El Mínimo sería = 11.50 ! 0.89 ! 0.74

Estos datos en una sola consulta.
Ya lo hice asi pero no me funciona:

Select Max(Tope) TopeMax,TopeLista ,TopeMay,
Min(Tope) TopeMin,TopeLista ,TopeLista
From Tabla1_marcas
Where Marca='CAJAS 44' and
Grupo='CARTON'
Group By TopeLista,TopeMay
Por lo que quiero que se me visualice asi:
TopeMax ! TopeLista ! TopeMay ! TopeMin ! TopeLista1 !TopeMay1
11.84 ! 0.79 ! 0.68 ! 11.50 ! 0.89 ! 0.74
Cualquier sugerencia se los agradecería muchisimo.

__cadetill · #2 02-09-2004

La consulta que quieres realizar, si bien es factible, seguramente te será muy lenta de la única forma que, a estas horas de la mañana se me ocurre.

Debido a que quieres el máximo y el mínimo, has de hacer subconsultas pero, una subconsulta sólo puede devolver un valor, por lo que se ha de lanzar una subconsulta para cada uno de los valores que quieres retornar, es decir, 6

Por esto, yo te recomiendo otra cosa, seguramente mucho más eficaz. O bien te creas un procedimiento almacenado que te devuelva esos valores, o bien lanzas una UNION del max y min y, por programa, montas la estructura deseada

AGAG4 · #3 02-09-2004

Muchas Gracias Sr. Xavier Matínez por su respuesta voy a tomar en cuenta su sugerencia... De antemano Gracias....!!!!!

scooterjgm · #4 02-09-2004

yo pienso q deberias de hacer dos consultas unidas del siguiente modo:

select TOPE,TOPELISTA,TOPEMAY
from .....
WHERE TOPE=MAX(TOPE) or TOPE=MIN(TOPE)
group by......

pruebalo y me dices...

AGAG4 · #5 02-09-2004

Ya lo probe, una pregunta como se haria en el caso de la intrucción "UNION" ???? para juntar el Máximo y el Mínimo con sus respectivos Topes.

Ahora hice pruebas como me dijo pero me marca un error, que me indica que tengo que poner la Clausula Having despues del Group By, por lo que hago esto:

Select TOPE,TOPELISTA,TOPEMAY
From Tabla_Marcas
WHERE Marca='BIRLOS' and
Grupo='TRACK'
group by TOPE,TOPELISTA,TOPEMAY
HAVING TOPEDOLAR=MAX(TOPEDOLAR) or
TOPEDOLAR=MIN(TOPEDOLAR)

funciona diferente a lo que quiero, que me visualice Solo un registro en donde aparezca el Tope Máximo con sus Topes(TOPELISTA,TOPEMAY) junto con el Tope Mínimo también con sus Topes(TOPELISTA,TOPEMAY) . De antemano Gracias por contestarme....

guillotmarc · #6 02-09-2004

Hola.

En este caso, no estoy de acuerdo con Xavi. Con los índices adecuados esa consulta con dos subconsultas será completamente eficaz (y si no lo he entendido mal, tendria el mismo rendimiento que calcularlo mediante un procedimiento almacenado).

NOTA : En este caso, el índice adecuado no es un índice sobre varios campos, sinó un índice compuesto, es decir un índice por varios campos (los campos de la agrupación más el campo por el que se maximiza).

Saludos.

guillotmarc · #7 02-09-2004

Hola.

Cita:

Empezado por AGAG4

Ya lo probe, una pregunta como se haria en el caso de la intrucción "UNION" ???? para juntar el Máximo y el Mínimo con sus respectivos Topes.

Un UNION es para unir varias filas, y no para unir varios campos en una sola fila.

es decir :

select NOMBRE, APELLIDOS from CLIENTES
union
select NOMBRE, APELLIDOS from PROVEEDORES

Te va a sacar todos los registros de las tablas Clientes y Proveedores.

Saludos.

guillotmarc · #8 02-09-2004

Hola.

La consulta más o menos va a ser :

select
(select max(TOPE) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') as MAX_TOPE,
(select max(TOPELISTA) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') as MAX_TOPELISTA,
(select max(TOPEMAY) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') as MAX_TOPEMAY,
(select min(TOPE) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') as MIN_TOPE,
(select min(TOPELISTA) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') as MIN_TOPELISTA,
(select min(TOPEMAY) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') as MIN_TOPEMAY
from RDB$DATABASE

Como ha dicho Xavi tienes 6 subconsultas, y tienes que crear 3 índices : MARCA + GRUPO + TOPE, MARCA + GRUPO + TOPELISTA, MARCA + GRUPO + TOPEMAY para que sea completamente eficaz.

NOTA: Dado que solo hay que retornar una fila, he basado la consulta principal en la tabla RDB$DATABASE, que es una tabla de sistema, que sabemos que siempre existe y siempre devuelve un solo registro. (Aunque podrias haber utilizado cualquier tabla tuya, que sepas seguro que siempre contiene un único registro).

Saludos.

guillotmarc · #9 02-09-2004

Por cierto, no debo haber entendido bien lo que se está buscando, puesto que ¿ que problema hay con esta simple consulta ? :

select max(TOPE) as MAX_TOPE, max(TOPELISTA) as MAX_TOPELISTA, max(TOPEMAY) as MAX_TOPEMAY, min(TOPE) as MIN_TOPE, min(TOPELISTA) as MIN_TOPELISTA, min(TOPEMAY) as MIN_TOPEMAY
from TABLA1_MARCAS
where MARCA = 'CAJAS 44' and GRUPO = 'CARTON'

AGAG4 · #10 02-09-2004

Estoy de acuerdo con usted, pero solo quiero el valor Máximo y Mínimo de "TOPE", pero no quiero el Máximo de los otros dos campos "TopeLista" y "TopeMayoreo", no se si me he explicado????

Sólo quiero 1 registro en donde aparezca el Tope Máximo con sus Topes(TOPELISTA,TOPEMAY) junto con el Tope Mínimo también con sus Topes(TOPELISTA,TOPEMAY) .
LOS TOPES: TOPELISTA y TOPEMAY no los quiero como Máximos

De antemano Gracias por contestarme....

antrax · #11 02-09-2004

haber si esto soluciona:

SELECT A.MAXIMO,A.TOPELISTA,A.TOPEMAY,B.MINIMO,B.TOPELISTA,B.TOPEMAY
FROM
(SELECT MAX(TOPE),TOPELISTA,TOPEMAY FROM TuTabla WHERE ...) AS A
INNER JOIN
(SELECT MIN(TOPE),,TOPELISTA,TOPEMAY FROM TuTabla WHERE ...) AS B
ON A.LLAVE=B.LLAVE{aqui pones un campo llave, pero no se la estructura completa de tu tabla...}
WHERE CAJAS=44 AND GRUPO=CARTON

Lo que hará el INNER JOIN será juntar las dos parciales a y b en un solo registro, pero deberas encontrar la coincidencia, es decir el campo que los pueda unir, si pudieses explicar un poquito mas el fin que tendra tu consulta, asi como un poquito mas de tu estructura de BD, podré ayudarte mejor...

No puedo ser mas especifico porque incluso no se bien cual es el fin de la consulta, que es lo que va a mostrar en tu aplicación pero estamos
pendientes...

AGAG4 · #12 03-09-2004

Gracias de antemano por respondermer, pero no me funciono lo que dice...

Código SQL [-]SELECT A.MAXIMO,A.TOPELISTA,A.TOPEMAY,B.MINIMO,B.TOPELISTA,B.TOPEMAY 
FROM 
/*Marca Error en Select*/(SELECT MAX(TOPE),TOPELISTA,TOPEMAY FROM   TuTabla WHERE ...) AS A
INNER JOIN
(SELECT MIN(TOPE),,TOPELISTA,TOPEMAY FROM TuTabla WHERE ...) AS B
ON A.LLAVE=B.LLAVE /*aqui pones un campo llave, pero no se la estructura completa de tu tabla...*/

WHERE CAJAS=44 AND GRUPO=CARTON
Me marca Error en la Segundo "Select" Token unknown, la llave ya la puse va sería A.Marca=B.Marca

Y como ya lo habia comentado el objetivo de esta consulta es de que me muestre el Máximo y el Mínimo valor del Campo TOPE en una sola consulta que a su vez me muestre los Campos TOPELISTA y TOPEMAY (que no van hacer por el Máximo ó Mínimo)
Tengo la siguiente tabla como Ejemplo:
TOPE ! TOPELISTA ! TOPEMAY
-------------------------------
11.50 ! 0.89 ! 0.74
11.66 ! 0.99 ! 0.78
11.80 ! 0.89 ! 0.78
11.84 ! 0.79 ! 0.68

Por lo que quiero que se me visualice asi en una sola consulta:
TopeMax ! TopeLista ! TopeMay ! TopeMin ! TopeLista1 !TopeMay1
11.84 ! 0.79 ! 0.68 ! 11.50 ! 0.89 ! 0.74

Si redacte mal por favor digame... Gracias.....

__cadetill · #13 03-09-2004

Antes de empezar con el rollo...... AGAG4, te he editado el mensaje para poner la etiqueta [ sql ] (sin espacios). Mírate el funcionamiento de ésta y otras leyendo el primer mensaje del cualquier foro.

Ahora, al grano:

Veamos, lo que el amigo AGAG4 quiere, si no he entendido mal, es lo siguiente:

Como vemos, se lanzan muuuchas subselect, por lo que, por mucho que diga el amigo Marc de poner/crear los índices adecuados (

), siento no estar de acuerdo con él. Quiera o no, esta multitud de subselect se vuelve muy lento. Además hay que tener presente que alguna de estas consultas (si TOPE no es índice o Unique) puede devolver más de 1 fila, por lo que habría que poner la cláusula distinct y esto aun lo haría más lento.

Quizás con tablas pequeñas la diferencia sea poca, pero con tablas con muchos datos......

He realizado mis pruebas contra una tabla de más de 250 mil de registros en DB400 (DB2 para AS400) con las sentencias siguientes sentencias SQL

SQL1

Código SQL [-]select 
  (select max(campo1) from tabla),
  (select campo2 from tabla where campo1 = (select max(campo1) from tabla) ),
  (select distinct campo3 from tabla where campo1 = (select max(campo1) from tabla) ),
  (select min(campo1) from tabla),
  (select campo2 from tabla where campo1 = (select min(campo1) from tabla) ),
  (select distinct campo3 from tabla where campo1 = (select min(campo1) from tabla) ),
from tiqcap

SQL2

Código SQL [-]select campo1, campo2, campo3
from tabla
where
  campo1 = (select max(campo1) from tabla) or campo1 = (select min(campo1) from tabla)

SQL3

Código SQL [-]select campo1, campo2, campo3
from tabla
where
  campo1 = (select max(campo1) from tabla)
UNION
select campo1, campo2, campo3
from tabla
where
  campo1 = (select min(campo1) from tabla)

Con los siguientes resultados:
SQL1: 1:57.213
SQL2: 0:07.411
SQL3: 0:10.175

Como véis, en el SQL1 he tenido que poner un distinct ya que esa subconsulta me devolvía más de un resultado. Por otro lado, también es cierto que con SQL1 tenemos exactamente lo que queremos, pero con un gran coste. Quizás mejor programar 4 lineas de código y reducir el coste de la consulta de forma drástica.

Creo que hay una clara diferencia entre la cantidad de subselects y los demás métodos, siendo el más rápido el propuesto por el amigo scooterjgm.

Lo que no he probado es lo del procedimiento almacenado, pero supongo que los tiempos serán por el estilo al SQL2 y SQL3

Pues nada, estas han sido las pruebas

Espero que os sean de utilidad

guillotmarc · #14 03-09-2004

Hola.

Aqui tienes el mismo resultado, aunque con solo dos subconsultas, por lo que el rendimiento tiene que ser óptimo.

Código SQL [-]select MAX.TOPE as MAX_TOPE, MAX.TOPELISTA as MAX_TOPELISTA, MAX.TOPEMAY as MAX_TOPE_MAY,
         MIN.TOPE as MIN_TOPE, MIN.TOPELISTA as MIN_TOPELISTA, MIN.TOPEMAY as MIN_TOPE_MAY
from RDB$DATABASE
inner join TABLA1_MARCAS MAX where TOPE = (select max(TOPE) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') and
                                                     MARCA = 'CAJAS 44' and GRUPO = 'CARTON'
inner join TABLA1_MARCAS MIN where TOPE = (select min(TOPE) from TABLA1_MARCAS where MARCA = 'CAJAS 44' and GRUPO = 'CARTON') and
                                                     MARCA = 'CAJAS 44' and GRUPO = 'CARTON'

Saludos.

__cadetill · #15 03-09-2004

jejeje, pero esa consulta te funciona porque es IB/FB (si si, ya se que es el motor que tratamos), pero en otro motor no te funcionaría y se tendría que buscar un "truquillo" parecido a este para no tener que hacer la multitud de subselects

guillotmarc · #16 03-09-2004

Hola.

Supongo que te refieres a la utilización de RDB$DATABASE.

En realidad yo nunca la utilizo (ni tan solo con IB/FB), solo necesitamos utilizar una tabla con un único registro, y para ellos utilizo una tabla propia (la tabla CONFIG donde guardo un único registro de configuración).

Utilizando una tabla propia con un solo registro, esa consulta funciona en cualquier servidor SQL, puesto que es SQL estándar.

Saludos.

__cadetill · #17 03-09-2004

Cita:

Empezado por guillotmarc

Supongo que te refieres a la utilización de RDB$DATABASE.

si si, a esa tabla me refería

Cita:

Empezado por guillotmarc

En realidad yo nunca la utilizo (ni tan solo con IB/FB), solo necesitamos utilizar una tabla con un único registro, y para ellos utilizo una tabla propia (la tabla CONFIG donde guardo un único registro de configuración).

Es que yo no tengo ninguna tabla con 1 sólo registro

(si si, pordía crearla

)

Cita:

Empezado por guillotmarc

Utilizando una tabla propia con un solo registro, esa consulta funciona en cualquier servidor SQL, puesto que es SQL estándar.

Correcto, mu buen truquillo, me lo anoto en mi libretilla

AGAG4 · #18 03-09-2004

Nunca me imagine que me contestarán tantas Personas, la verdad estoy sorprendido la manera en que ayudan a los demás, estoy orgulloso de ustedes y de quienes integran este grupo de Moderadores, pues Muchísimas Gracias a todos los que me estan respondiendo.
Ya realice pruebas acerca de lo que me dijeron, primero para el Sr. Xavier Martínez:
Me funcionaron los 3 sentencias SQL, la verdad Muchas Gracias me visualizan en dos registros pero es correcto.

Para el Sr. GillotMarc, me produce un error en el primer "WHERE" del primer "INNER JOIN", ya le movi para que me funcionará con los dos JOIN pero no di con ninguno, pero de todas formas Gracias por contestarme se los agradezco muchoo... Que tengan buen día

AGAG4 · #19 03-09-2004

Con las Sentencias que me dio el Sr. Xavier Martínez, una de ellas es esta....

Select TOPEDOLAR,TOPELISTAPUB,TOPELISTAMAY,TOPEPROPUB,TOPEPROMAY
from tinv_dmarcagrupo
where
Marca=:Marca and Grupo=:Grupo and
TOPEDOLAR = (select max(TOPEDOLAR) from tinv_dmarcagrupo)
UNION
Select TOPEDOLAR,TOPELISTAPUB,TOPELISTAMAY,TOPEPROPUB,TOPEPROMAY
from tinv_dmarcagrupo
where
Marca=:Marca and Grupo=:Grupo and
TOPEDOLAR = (select min(TOPEDOLAR) from tinv_dmarcagrupo)
into :TOPEDOLAR, :TOPELISTAPUB,:TOPELISTAMAY,:TOPEPROPUB,:TOPEPROMAY;
suspend;

Tengo este código en un Proc. Almacenado y al quererlo ejecutar en con un "ibStoredProc" me marca el siguiente error "Multiples Rows in Singleton Select", pero lo raro que si hago esto en el SQL Editor con IbExpert no me marca el error, y si lo hago en "Input Parameters" dentro del Proc. Alamcenado dentro de IbExpert, tambien me marca el mismo error. Pasa con las 3 sentencias SQL.