Search set of records by date

Asked

Viewed 61 times

0

I am currently creating a Java/Mysql Desktop program. When inserting into the bank, the table is very simple:

id - dataInserido - códigoProduto - qtd

I would like, through an SQL, to group in descending order the last records entered on the same date. Example: I have atabela of inserted products 

id - data - produto - qtd  
1 - 20/05/2017 - 10001 - 100  
1 - 24/05/2017 - 10002 - 10  
1 - 27/05/2017 - 100010 - 30  
1 - 29/05/2017 - 100020 - 60  
1 - 29/05/2017 - 100060 - 70  
1 - 29/05/2017 - 100010 - 100

How to group the last ones (05/29/2017), being 100020, 100060 and 100010, since I have to mount this table in Java ? A kind of filter requested by the user.

ps: I don’t want to take any date by java, but rather a query by sql itself.

java mysql

  • Puts an example of the output you want

  • If I understand your question (because I’m not so good at Java yet), I always do this: while (rs.next()) { List.add(new Object[]{rs.getInt("id")}); } I still haven’t defined how the output and how I’m going to get the values in Java. If you can also suggest something, thank you. This challenge has now appeared since I started

  • A simple SELECT * FROM tabela ORDER BY dataInserido DESC would not solve?

  • Not much. The bank would pick up all the records and order. I would like only the last date of the registered products.

  • Or if you wanted to bring the records only on the present day: SELECT * FROM tabela WHERE dataInserido = CURDATE()

  • Thanks. Your suggestion for the "Launched Today" filter I’m doing will be helpful too.

Show 1 more comment

1 answer

1


What you need is to filter through the column data using the clause WHERE with NOT EXISTS:

SELECT t.id,
       t.data,
       t.produto,
       t.qtd
  FROM tabela t
 WHERE NOT EXISTS(SELECT 1
                    FROM tabela t2
                   WHERE t2.data > t.data)
 ORDER BY t.produto DESC

The clause ORDER BY will sort the records according to the column that was specified.

Note that I respected the column name given in the example and not in its description.

  • So as you mentioned I would have to take a date from Java to Mysql, wouldn’t I? The table is filled daily and will always have products, date, and Qtd released daily. The way I’m building the user chooses in Combobox the option "Filter Latest Date" and this action brings to the table the products of the last release. SELECT Cód, Qtd, data FROM controle_diario GROUP BY data ORDER BY data DESC LIMIT 1;

  • @rafB altered to show all records from the last date

  • Perfect. Thank you Sorack. Problem solved this way as you did.

Browser other questions tagged java mysql

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