CODEWITHSUNDEEP
phpmysqlsqldatabase
Luca Scalvi
Luca Scalvi

Reputation: 81

Select MAX and MIN value of the last 7 records inserted

I need to select the MAX and the MIN value of the last 8 records inserted for a specific device. I have a table named letture with this structure:

id, id_dispositivo, id_utenza, id_impianto, id_lettura, data, valore

on this table I made a query to extract the last 8 records for id_dispositivo = 1

SELECT valore FROM `letture` WHERE id_dispositivo = 1 ORDER BY id_lettura DESC LIMIT 0,8

On the result of this query I need to catch the MAX and the MIN value

Upvotes: 1

Views: 159

Answers (3)

Bidur Karki
Bidur Karki

Reputation: 1

---for sql server 2008 

Create Table Testing(TestID Integer Primary Key,Price Float);
Insert Into Testing Values (1,12.00);
Insert Into Testing Values (2,15.00);
Insert Into Testing Values (3,20.00);
Insert Into Testing Values (4,13.00);
Insert Into Testing Values (5,11.00);
Insert Into Testing Values (6,12.00);
Insert Into Testing Values (7,17.00);
Insert Into Testing Values (8,18.00);
Insert Into Testing Values (9,19.00);
Insert Into Testing Values (10,22.00);

Create Function dbo.GetMinID(@MaxId Integer,@NoOfRows Integer) Returns Integer
as 
Begin
    Declare @MinMax Integer
    IF @NoOfRows > 1  
    Set @MinMax=dbo.GetMinID((Select MAX(TestID) From Testing where TestID <@MaxId),@NoOfRows-1)
    else 
    Set @MinMax=(Select MAX(TestID) From Testing where TestID < @MaxId);                
    Return @MinMax;
End 

Select R.TestID,dbo.GetMinID(R.TestID,8) as MinMax From (Select MAX(TestID) TestID
From Testing) R

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33531

Just use a subselect:

SELECT MIN(valore), MAX(valore) FROM (
  SELECT valore 
  FROM `letture` 
  WHERE id_dispositivo = 1 ORDER BY id_lettura DESC LIMIT 0,8
) T1;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You need a subquery:

SELECT MIN(valore), MAX(valore)
FROM (SELECT valore
      FROM `letture`
      WHERE id_dispositivo = 1
      ORDER BY id_lettura DESC
      LIMIT 8
     ) last8;

Upvotes: 3

Related Questions