Extract only the topline of data from a specific table SQL

Question

I'm having trouble extracting the topline of data from a table and joining it with other extracted fields from other tables.

I have 3 tables:

Person
Folder
Earnings

Person:

PERSONID |FORENAMES|SURNAME|DOB      |GENDER|NINO

1000000  |JOHNSTON |ALI    |10/10/80 |M     |JK548754A

Folder:

FOLDERID|FOLDERREF

1000000 |104567LK

Earnings:

FOLDERID|DATESTARTED|DATEENDED  |GROSSEARNINGS 

1000000 |01-04-2014 |31-03-2015 |31846.00      
1000000 |01-04-2013 |31-03-2014 |31160.04

1000000 |01-04-2012 |31-03-2013 |30011.04

1000000 |01-04-2011 |31-03-2012 |29123.94

I need my data to look like:

JOHNSTON |ALI| 10-10-1980 | 31-03-2015 | 31846.00 | 31649.60

I've tried:

SELECT A.PERSONID, A.SURNAME, A.FORENAMES, A.DOB, B.FOLDERREF, C.DATEENDED, C.GROSSEARNINGS, C.BASICEARNINGS, C.FLUCTUATINGEARNINGS

FROM PERSON A, FOLDER B, EARNINGS C

WHERE A.PERSONID = B.FOLDERID AND B.FOLDERID = C.FOLDERID

Which extracts all of the data from the EARNINGS table, but I only wish to extract the top line.

Any advice is greatly received.

Is this MySQL or SQL Server? They have different syntax for limiting the number of records returned. You will also want to use an `ORDER BY` clause to define the sort order of the records. Otherwise "the top line" doesn't really mean anything. — David, Apr 25 '16 at 12:55
Possible duplicate of [How to select the nth row in a SQL database table?](http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table) — Tab Alleman, Apr 25 '16 at 13:11

score 0 · Accepted Answer · answered Apr 25 '16 at 13:11

If you want just the data from the latest date then you could do something like the query below. Bear in mind, you're using fields like c.BasicEarnings and c.FluctuatingEarnings that you don't have in table 'Earnings'

SELECT a.PersonID
    ,a.Suranme
    ,a.Forenames
    ,a.DOB
    ,b.FolderRef
    ,c.DateEnded
    ,c.GrossEarnings
FROM Person a
JOIN Folder b ON a.FolderID = b.FolderID
JOIN (
    SELECT e.FolderID
        ,e.DateEnded
        ,e.GrossEarnings
    FROM Earnings e
    JOIN (
        SELECT FolderID
            ,MAX(DateEnded) DateEnded
        FROM Earnings
        GROUP BY FolderID
        ) m ON e.FolderID = m.FolderID
        AND e.DateEnded = m.DateEnded
    ) c ON a.FolderID = c.FolderID

score 0 · Answer 2 · answered Apr 25 '16 at 13:47

Assuming the final field in your expected output is GROSSEARNINGS and by "I only wish to extract the top line" you mean latest (by date) then use GROUP BY with a MAX function.

SELECT p.FORENAMES, p.SURNAME, p.DOB, MAX(e.DATEENDED), e.GROSSEARNINGS, e.BASICEARNINGS
FROM Person p
INNER JOIN Earnings e ON p.PERSONID = e.FOLDERID
GROUP BY p.FORENAMES, p.SURNAME, p.DOB, e.GROSSEARNINGS, e.BASICEARNINGS

Extract only the topline of data from a specific table SQL

2 Answers2