SQL - SQL, Mostrar todos los campos de la base

Tendo la consulta siguiente

SELECT CODIGO, INDICE, DESTINO FROM #test
WHERE DESTINO>"500000"
UNION
SELECT CODIGO , MAX(INDICE), 0 FROM #test
WHERE CODIGO<"500000"
GROUP BY CODIGO
ORDER BY CODIGO

Pero tengo dos problemas:

1) Cuando completo los select para que me muestre los demas campos de la base, me modifica la consulta ya que me los pide en el group by.

2)Me debería mostrar todos los registros cuyo destino es > que 500000 o cuyo indice es el mayor y el destino es 0; pero me muestra todos cuyo destino es > que 500000 y el ultimo indice que esta en 0

ej:

25 1 0
25 2 0
25 3 15

Me deberia Mostrar

25 3 15

y me muestra

25 3 15
25 2 0

Desde ya mil gracias,

Andrea,
Un ejemplo vale más que mil palabras.
Este pedido no es lo mismo que el anterior, si pusieras un rango de datos más amplio donde se cumplan todas las condiciones, es posible que encontremos la solución que necesitás, espero tus datos.
Saludos,
Liliana.

Andrea,
sobre estos datos, decime cuáles se mostrarían:

25, 1, 0
25, 2, 500000
25, 3, 0

26, 1, 500000
26, 2, 0
26, 3, 15

27, 1, 500000
27, 2, 500000
27, 3, 500000

28, 1, 0
28, 2, 16

29, 1, 20
29, 2, 0

Otra cosa, si estás mostrando más datos de la misma fila, que no estén en una función de agregado (MAX, MIN, AVG, SUM, etc), tendrán que estar incluídos en el GROUP BY, es por eso que puede darte error.

En el ORDER BY puede ponerse el número de columna en lugar de su nombre o expresión, por eso el ORDER BY 1, 2, significa ordenado por columna 1, columna 2.

Liliana.

Liliana:

Tu info me sirvio de mucho

el tema es que por registro tengomuchos datos para mostrar, pero como decis vos un ej, vale mas que 1000 palabras:

base:
codigo item destino descrip observ observ2 referencia costo

25, 1, 0 ,prueba01,nada,xxx,XXX,25.30
25, 2, 500000,prueba01,nada,xxx,XX,25.40
25, 3, 0,prueba02,nada,xx,XXX,24

26, 1, 500000,prueba03,nada,xx,XXX,30
26, 2, 0,prueba04,nada,xx,X,20
26, 3, 15,prueba03,nada,x,XXX,28

27, 1, 500000,prueba01,nada,xx,XXX,4
27, 2, 500000,prueba02,nada,xx,XX,2
27, 3, 0,prueba02,nada,x,XXX,4

y deberia ver:

25, 2, 500000,prueba01,nada,xxx,XX,25.40
25, 3, 0,prueba02,nada,xx,XXX,24
26, 1, 500000,prueba03,nada,xx,XXX,30
26, 3, 15,prueba03,nada,x,XXX,28
27, 1, 500000,prueba01,nada,xx,XXX,4
27, 2, 500000,prueba02,nada,xx,XX,2

como puedo hacer?

Andrea,
No entiendo cuál es el criterio para que no aparezca la fila
27, 3, 0,prueba02,nada,x,XXX,4 de tu ejemplo, pero imagino que con esta base vas a poder llegar a tu objetivo:

El código sería así:
SELECT * FROM #test WHERE destino = 500000
UNION
SELECT t1.* FROM #test t1
INNER JOIN
(SELECT codigo, item = MAX(item)
FROM #test
WHERE destino < 500000 GROUP BY codigo) t2
ON t1.codigo = t2.codigo AND t1.item = t2.item
ORDER BY 1, 2

Y el resultado es:
codigo item destino descrip observ observ2 referencia costo
------ ------ ----------- -------------------- ---------- ---------- ---------- ------------
25 2 500000 prueba01 nada xxx XX 25.40
25 3 0 prueba02 nada xx XXX 24.00
26 1 500000 prueba03 nada xx XXX 30.00
26 3 15 prueba03 nada x XXX 28.00
27 1 500000 prueba01 nada xx XXX 4.00
27 2 500000 prueba02 nada xx XX 2.00
27 3 0 prueba02 nada x XXX 4.00

Esta es la consulta que hice segun me pasaste.

SQL.Add('SELECT * FROM Desa WHERE destino > 500000 ');
SQL.Add('UNION ');
SQL.Add('SELECT t1.* FROM Desa t1 [INNER] JOIN (SELECT CODIGO, INDICE=MAX(INDICE) FROM Desa WHERE destino < 500000 GROUP BY CODIGO) t2 ') ;
SQL.Add('ON t1.codigo = t2.codigo AND t1.indice = t2.indice ');
SQL.Add('ORDER BY 1, 2 ');

el tema es que me devuelve

todos los que tienen destino 0
y duplicados los que tienen destino >500000

andrea

PD: Feliz Dia

Andrea,
Con los datos que mandaste de prueba, armé la tabla, escribí el query, y logré los resultados que te mostré, fijate por favor de copiar y pegar el código para verlo.
¿Será posible que no se estén cumpliendo todas las condiciones que tenés en la información real?

create table #test
(codigo smallint, item smallint, destino int, descrip char(20), observ char(10), observ2 char(10), referencia char(10), costo dec(10,2))

insert #test

select 25, 1, 0 ,'prueba01','nada','xxx','XXX',25.30 union
select 25, 2, 500000,'prueba01','nada','xxx','XX',25.40 union
select 25, 3, 0,'prueba02','nada','xx','XXX',24 union

select 26, 1, 500000,'prueba03','nada','xx','XXX',30 union
select 26, 2, 0,'prueba04','nada','xx','X',20 union
select 26, 3, 15,'prueba03','nada','x','XXX',28 union

select 27, 1, 500000,'prueba01','nada','xx','XXX',4 union
select 27, 2, 500000,'prueba02','nada','xx','XX',2 union
select 27, 3, 0,'prueba02','nada','x','XXX',4

Con el query que publiqué anteriormente:

SELECT * FROM #test WHERE destino = 500000
UNION
SELECT t1.* FROM #test t1
INNER JOIN (SELECT codigo, item = MAX(item) FROM #test WHERE destino < 500000 GROUP BY codigo) t2
ON t1.codigo = t2.codigo and t1.item = t2.item
ORDER BY 1, 2

Los resultados que se obtienen son:

codigo item destino descrip observ observ2 referencia costo
------ ------ ----------- -------------------- ---------- ---------- ---------- ------------
25 2 500000 prueba01 nada xxx XX 25.40
25 3 0 prueba02 nada xx XXX 24.00
26 1 500000 prueba03 nada xx XXX 30.00
26 3 15 prueba03 nada x XXX 28.00
27 1 500000 prueba01 nada xx XXX 4.00
27 2 500000 prueba02 nada xx XX 2.00
27 3 0 prueba02 nada x XXX 4.00

PD: Gracias, igualmente.