Duvida no select

Delphi

19/10/2010

Tenho o seguinte select
select
SW2030.W2_PO_DT,
SW3030.W3_PO_NUM,
SW3030.W3_COD_I,
SW3030.W3_QTDE,
SW3030.W3_PRECO,
SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL
from SW3030
LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM
WHERE
SW3030.W3_NR_CONT <> 0
AND
SW3030.D_E_L_E_T_=''       
ORDER BY SW3030.W3_COD_I
 
O resultado do select acima: W2_PO_DT  W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/02/2010  BMM 179930      ALDB0.1FOO11BC  800 5,1 4080
15/03/2010  BMM 179940      ALDB0.1FOO11BC  850 5,1 4335
15/04/2010  BMM 179950      ALDB0.1FOO11BC  800 5,1 4080
15/05/2010  BMM 179960      ALDB0.1FOO11BC  850 5,1 4335
15/06/2010  BMM 179940      ALDB0.1FOO11OL  650 5,1 3315
15/07/2010  BMM 179930      ALDB0.1FOO11OL  650 5,1 3315 Gostaria de um select que o resultado saisse assim: W2_PO_DT  W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/05/2010  BMM 179960      ALDB0.1FOO11BC  850 5,1 4335
15/07/2010  BMM 179930      ALDB0.1FOO11OL  650 5,1 3315 Sempre o ultimo da data W2_po_dt  
Como eu posso fazer isso em um unico select
Evandro Abreu

Evandro Abreu

Curtidas 0

Respostas

William Galleti

William Galleti

19/10/2010

Olá Evandro.
Se você quiser agrupar sem somar, basta usar o Group By. Ex:
SELECT SW2030.W2_PO_DT,       SW3030.W3_PO_NUM,       SW3030.W3_COD_I,       SW3030.W3_QTDE,       SW3030.W3_PRECO,       SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL  FROM SW3030  LEFT JOIN SW2030 ON SW2030.W2_PO_NUM = SW3030.W3_PO_NUM WHERE SW3030.W3_NR_CONT <> 0   AND SW3030.D_E_L_E_T_ = '' GROUP BY SW2030.W2_PO_DT,          SW3030.W3_PO_NUM,          SW3030.W3_COD_I,          SW3030.W3_QTDE,          SW3030.W3_PRECO,          SW3030.W3_QTDE * SW3030.W3_PRECO ORDER BY SW3030.W3_COD_I

Espero ter ajudado!
GOSTEI 0
Leonardo Xavier

Leonardo Xavier

19/10/2010

LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM
WHERE
SW3030.W3_NR_CONT <> 0
AND
SW3030.D_E_L_E_T_=''       
ORDER BY SW3030.W3_COD_I order by W2_po_dt desc
GOSTEI 0
Wilson Junior

Wilson Junior

19/10/2010

Teste assim
SELECT
  SW2030.W2_PO_DT,
  SW3030.W3_PO_NUM,
  SW3030.W3_COD_I,
  SW3030.W3_QTDE,
  SW3030.W3_PRECO,
  (SW3030.W3_QTDE * SW3030.W3_PRECO) AS VLR_TOTAL
FROM
  SW3030
    LEFT JOIN SW2030 ON (SW2030.W2_PO_NUM = SW3030.W3_PO_NUM)
WHERE
  SW3030.W3_NR_CONT <> 0
  AND SW3030.D_E_L_E_T_ = ''
  AND SW2030.W2_PO_DT =
    ( SELECT
        MAX(SW2030a.W2_PO_DT)
      FROM
        SW2030 SW2030a
      WHERE
        SW2030a.W2_PO_NUM = SW3030.W3_PO_NUM
    )
ORDER BY
  SW3030.W3_COD_I


Espero ter colaborado.
GOSTEI 0
Evandro Abreu

Evandro Abreu

19/10/2010

no exemplo que passou abaixo....esta aparecendo todos os registros.   no exemplo que passei, gostaria que aparecesse assim:     W2_PO_DT  W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/05/2010  BMM 179960      ALDB0.1FOO11BC  850 5,1 4335
15/07/2010  BMM 179930      ALDB0.1FOO11OL  650 5,1 3315
GOSTEI 0
Bruno Batista

Bruno Batista

19/10/2010

Boa tarde,

tente da seguinte maneira.

http://www.activedelphi.com.br/forum/viewtopic.php?p=311127#311127

no exemplo que passou abaixo....esta aparecendo todos os registros.   no exemplo que passei, gostaria que aparecesse assim:     W2_PO_DT  W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/05/2010  BMM 179960      ALDB0.1FOO11BC  850 5,1 4335
15/07/2010  BMM 179930      ALDB0.1FOO11OL  650 5,1 3315
GOSTEI 0
Bruno Batista

Bruno Batista

19/10/2010

select SW2030.W2_PO_DT,


       SW3030.W3_PO_NUM,


       SW3030.W3_COD_I,


       SW3030.W3_QTDE,


       SW3030.W3_PRECO,


       SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL


  from SW3030


  LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM


 WHERE SW3030.W3_NR_CONT <> 0


   AND SW3030.D_E_L_E_T_=''


   and (SW2030.W2_PO_DT, SW3030.W3_COD_I) in (select Max(s.W2_PO_DT) as data,


                                                     s.W3_COD_I


                                                from SW3030 s


                                               group by s.W3_COD_I)


 ORDER BY SW3030.W3_COD_I



Boa tarde,

tente da seguinte maneira.

http://www.activedelphi.com.br/forum/viewtopic.php?p=311127#311127

no exemplo que passou abaixo....esta aparecendo todos os registros.   no exemplo que passei, gostaria que aparecesse assim:     W2_PO_DT  W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/05/2010  BMM 179960      ALDB0.1FOO11BC  850 5,1 4335
15/07/2010  BMM 179930      ALDB0.1FOO11OL  650 5,1 3315
GOSTEI 0
Evandro Abreu

Evandro Abreu

19/10/2010

Esta dando erro na seguinte linha and (SW2030.W2_PO_DT, SW3030.W3_COD_I) in (select Max(s.W2_PO_DT) as data, s.W3_COD_I from SW3030 s group by s.W3_COD_I)           Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ','.
GOSTEI 0
Bruno Batista

Bruno Batista

19/10/2010

Boa tarde,

verifica se funciona assim.

select SW2030.W2_PO_DT,


       SW3030.W3_PO_NUM,


       SW3030.W3_COD_I,


       SW3030.W3_QTDE,


       SW3030.W3_PRECO,


       SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL


  from SW3030


  LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM


 WHERE SW3030.W3_NR_CONT <> 0


   AND SW3030.D_E_L_E_T_=''


   and (SW2030.W2_PO_DT, SW3030.W3_COD_I) in (select Max(s1.W2_PO_DT) as data,


                                                     s.W3_COD_I


                                                from SW3030 s

                                               LEFT JOIN SW2030 s1 on s1.W2_PO_NUM = s.W3_PO_NUM


                                               group by s.W3_COD_I)


 ORDER BY SW3030.W3_COD_I


Esta dando erro na seguinte linha and (SW2030.W2_PO_DT, SW3030.W3_COD_I) in (select Max(s.W2_PO_DT) as data, s.W3_COD_I from SW3030 s group by s.W3_COD_I)           Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ','.
GOSTEI 0
POSTAR