Access data source using SQL to show most recent entry per site

Question

First of all I am a complete beginner to SQL and have been thrown in at the deep end a bit ! I'm learning as I go along and each mistake I make or question I ask will hopefully help me develop... please be kind :)

I have a working query that extracts electricty meter readings and other information. I am after finding the most recent reading for each site. This is the query at the moment :

PARAMETERS [Site Group] Text ( 255 );
SELECT 
Lookup.Lookup_Name AS [Group], 
Contacts.Name AS Site, 
Points.Number AS MPAN, 
Max(DataElectricity.Date) AS MaxDate, 
DataElectricity.M1_Present, 
DataElectricity.M2_Present, 
DataElectricity.M3_Present, 
DataElectricity.M4_Present, 
DataElectricity.M5_Present, 
DataElectricity.M6_Present, 
DataElectricity.M7_Present, 
DataElectricity.M8_Present,
DataElectricity.Direct
FROM 
DataElectricity INNER JOIN (Lookup INNER JOIN (Points INNER JOIN Contacts ON Points.Contacts_Id = Contacts.Id) ON Lookup.Lookup_Id = Contacts.Group_1) ON DataElectricity.Point_Id = Points.Id
WHERE
((DataElectricity.Direct)='D')
GROUP BY 
Lookup.Lookup_Name, Contacts.Name, Points.Number, DataElectricity.M1_Present, DataElectricity.M2_Present, DataElectricity.M3_Present, DataElectricity.M4_Present, DataElectricity.M5_Present, DataElectricity.M6_Present, DataElectricity.M7_Present, DataElectricity.M8_Present, DataElectricity.Direct
ORDER BY 
Lookup.Lookup_Name, Contacts.Name, Max(DataElectricity.Date) DESC;

However this returns all the readings for a site rather than just the most recent... I'm sure this is simple but I can't figure it out.

Any advice or guidence is gratefully received :)

what DBMS (type of SQL) are you using? is it Access, SQL Server MySQL?? — Dev N00B, Jul 23 '13 at 14:30
you will need to use LAST() to bring back the most recient record. Maybe LAST(Contacts.Name) AS Site, not sure if it will work hence only a comment — Dev N00B, Jul 23 '13 at 14:35
What about HAVING DataElectricity.Date = Max(DataElectricity.Date) — JsonStatham, Jul 23 '13 at 14:59
See if the answers to this question can help you out: http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column - The question name sounds irrelevant, but the question author clarifies, and it sounds like your issue. — Ben C., Jul 23 '13 at 15:08

score 1 · Accepted Answer · answered Jul 23 '13 at 14:36

1

Can't you just use top 1 to get only the first result?

SELECT top 1 ...

answered Jul 23 '13 at 14:36

caspian

1,804
14
14

I think he wants the last record per site, not just to limit to one record. – jpw Jul 23 '13 at 14:38

CatParky · Answer 2 · 2013-08-08T10:57:41.250

I have evolved the code a bit further using caspian's suggestion of SELECT top 1... but am struggling to refine it further and produce the result I need.

    PARAMETERS [Site Group] Text ( 255 );
    SELECT 
    Lookup.Lookup_Name, 
    Contacts.Name AS Site, 
    Points.Number AS MPAN, 
    DataElectricity.M1_Present, 
    DataElectricity.M2_Present, 
    DataElectricity.M3_Present, 
    DataElectricity.M4_Present, 
    DataElectricity.M5_Present, 
    DataElectricity.M6_Present, 
    DataElectricity.M7_Present, 
    DataElectricity.M8_Present,
    DataElectricity.Direct

    FROM 
    (
    SELECT TOP 1 DataElectricity.Date AS MaxDate, 
            DataElectricity.M1_Present, 
            DataElectricity.M2_Present, 
            DataElectricity.M3_Present, 
            DataElectricity.M4_Present, 
            DataElectricity.M5_Present, 
            DataElectricity.M6_Present, 
            DataElectricity.M7_Present, 
            DataElectricity.M8_Present,
            DataElectricity.Point_id
    FROM 
    DataElectricity 
    ORDER BY MaxDate DESC
    )

    DataElectricity INNER JOIN (Lookup INNER JOIN (Points INNER JOIN Contacts ON Points.Contacts_Id = Contacts.Id) ON Lookup.Lookup_Id = Contacts.Group_1) ON DataElectricity.Point_Id = Points.Id
    WHERE 
    ((Lookup.Lookup_Name)=Lookup_Name) 
    ORDER BY 
    Lookup.Lookup_Name, Contacts.Name, MaxDate DESC;

I do have a Google Drive file showing a small example of the data tables and desired result with hopfully a clear guide as to how the tables connect.

https://docs.google.com/file/d/0BybrcUCD29TxWVRsV1VtTm1Bems/edit?usp=sharing

The actual data contains hundreds of Site Groups each with potentially hundreds of sites.

I would like my end users to be able to select the Site Group name from the Lookup.Lookup_Name list and for it to return all the relevant sites and readings.

.... I really hope that makes sense !

Access data source using SQL to show most recent entry per site

2 Answers2