Bring record of each ID with the latest change date

Asked

Viewed 121 times

0

I am working with Oracle database and PHP, need to return only one product each with the latest change date, the tables are as follows.

product

produto

price

preco

I have tried several types of query, but I always get duplicate results.

And trying to group with the following query

SELECT a.CODPROD, a.DESCRICAO, b.PVENDA, b.CODPROD FROM PCPRODUT a, PCPRECO b
GROUP BY b.CODPROD

I get the following error:

"ORA-00979: not a GROUP BY expression"

php sql oracle10g
  • 1

    Tried to group the query? how you tried the query?

  • When I try to group I get the following error: "ORA-00979: not a GROUP BY expression"

  • A consulta é "SELECT
 CODPROD,
 DESCRICAO, 
 PVENDA
 
FROM
 PCPRODUT, PCPRECO WHERE PCPRODUT.CODPROD = PCPRECO.CODPROD"

  • Edith the question and put the query you tried with the group by

  • All the columns that are in select must be in the group by

  • 1

    I performed the following query now, but the page is just clicking and does not return anything. "a. CODPROD, a.DESCRICAO, b.PVENDA, b.CODPROD, max(b.DATAALTER) FROM PCPRODUT a, PCPRECO b GROUP BY a.CODPROD, a.DESCRICAO, b.PVENDA, b.CODPROD"

  • When you test on the bench you get the desired result?

  • No, the same thing happens

Show 3 more comments

1 answer

0

Dude, I haven’t used the Oracle in a while, try something like this:

SELECT a.CODPROD, a.DESCRICAO, b.PVENDA, b.CODPROD, MAX(b.DATAALTER)
FROM PCPRODUT a, 
INNER JOIN PCPRECO b ON a.CODPROD = b.CODPROD
GROUP BY a.CODPROD, a.DESCRICAO, b.PVENDA, b.CODPROD

  • Now pulled, but still with some duplicate results. Ex: 40 REFRESCO PO QUALIMAX PINEAPPLE 1 KG 4.51 40 2013-04-05 20:40:55 40 REFRESCO PO QUALIMAX PINEAPPLE 1 KG 5.34 40 2015-03-20 07:08:03 40 REFRESCO PO QUALIMAX PINEAPPLE 1 KG (null) 40 2015-06-08 00:00:00 74 REFRESCO PO QUALIMAX ORANGE 1 KG 7.74 74 2016-08-05 19:15:59

  • see if it helps http://forum.imasters.com.br/topic/512214-selecionando-item-max/#entry2030107

  • Helped, thank you!

Browser other questions tagged php sql oracle10g

You are not signed in. Login or sign up in order to post.