Home / Questions / 267252
Accepted
cosito
Asked: 2020-05-28 10:55:24 +0800 CST 2020-05-28 10:55:24 +0800 CST

Get the last record in a SQL query according to date

  • 772

I have a table that has these columns, comprapurchase date serial yserial date query` :. Necesito sacar los datos deyde acuerdo a la última he intentado hacerlo mediante este

SELECT compra,serial FROM warehouse where  MAX(fecha);

But it sends me this error:

group function is not allowed here

I don't need to obtain the information by means of an id or something similar, only the last purchase and serial according to the date.

sql

3 Answers

  1. SELECT compra,serial 
FROM warehouse 
where fecha = (SELECT MAX(fecha) 
FROM warehouse);

    Try this because an aggregation function directly with a where is not valid, better a subquery where you indicate that the date is equal and inside the parentheses you put the selection of the greater date.

    Or you can put the subquery inside a CTE (if your manager supports this feature) being like this

    WITH MaxDate AS (
    SELECT id, compra, serial FROM warehouse WHERE fecha = (SELECT MAX(fecha)FROM warehouse)
)

    Later you make the SELECTend of the alias that you created called MaxDate, leaving all your code in this way

    WITH MaxDate AS (
    SELECT id, compra, serial FROM warehouse WHERE fecha = (SELECT MAX(fecha)FROM warehouse)
)
SELECT id, compra, serial FROM MaxDate;
    • 3
  2. Best Answer

    You could do a select inside your where:

     SELECT w.compra,w.serial FROM warehouse w 
 WHERE w.fecha = (SELECT MAX(w.fecha) from warehouse)
    • 2

  3. Greetings, I suggest you do the following:

    SELECT campo1, campo2 FROM miTabla
WHERE fecha IN (SELECT max(fecha) FROM miTabla)

    I suggest you check the following: Max date

    • 1