Select query with max date

Question

I have this query

SQL query: selecting by branch and machine code, order by branch and date

SELECT  
    mb.machine_id AS 'MachineId',
    MAX(mb.date) AS 'Date',
    mi.branch_id AS 'BranchId',
    b.branch AS 'Branch',
    b.branch_code AS 'BranchCode'
FROM
    dbo.machine_beat mb
    LEFT JOIN dbo.machine_ids mi
    ON mb.machine_id = mi.machine_id
    LEFT JOIN dbo.branches b
    ON mi.branch_id = b.lookup_key
GROUP BY 
    mb.machine_id,
    mi.branch_id,
    b.branch,
    b.branch_code
ORDER BY 
    b.branch, [Date] DESC

Query result:

|==========|=======================|=========|==========|==========|
|MachineId |Date                   |BranchId |Branch    |BranchCode|
|==========|=======================|=========|==========|==========|
|SS10000005|2014-03-31 19:10:17.110|3        |Mamamama  |MMMM      |
|SS10000043|2014-03-31 17:16:32.760|3        |Mamamama  |MMMM      |
|SS10000005|2014-02-17 14:58:42.523|3        |Mamamama  |MMMM      |
|==================================================================|

My problem is how to select the updated machine code? Expected query result:

|==========|=======================|=========|==========|==========|
|MachineId |Date                   |BranchId |Branch    |BranchCode|
|==========|=======================|=========|==========|==========|
|SS10000005|2014-03-31 19:10:17.110|3        |Mamamama  |MMMM      |
|==================================================================|

Update I created sqlfiddle. I also added data, aside from MMMM. I need the updated date for each branch. So probably, my result will be:

|==========|=======================|=========|==========|==========|
|MachineId |Date                   |BranchId |Branch    |BranchCode|
|==========|=======================|=========|==========|==========|
|SS10000343|2014-06-03 13:43:40.570|1        |Cacacaca  |CCCC      |
|SS30000033|2014-03-31 18:59:42.153|8        |Fafafafa  |FFFF      |
|SS10000005|2014-03-31 19:10:17.110|3        |Mamamama  |MMMM      |
|==================================================================|

The problem is `machineId`. We need to see starting sample data for these results to better help you. — Clockwork-Muse, Jun 03 '14 at 09:37
@Clockwork-Muse I'll be back I'll try to replicate my table to sqlfiddle. Thanks in advance! — naru, Jun 03 '14 at 10:11

vhadalgi · Answer 1 · 2014-06-03T09:09:54.370

2

Try using Row_number with partition by

     select * from 
        (
        SELECT  
            mb.machine_id AS 'MachineId',
            mb.date AS 'Date',
            mi.branch_id AS 'BranchId',
            b.branch AS 'Branch',
            b.branch_code AS 'BranchCode',rn=row_number()over(partition by mb.machine_id order by mb.date desc)
        FROM
            dbo.machine_beat mb
            LEFT JOIN dbo.machine_ids mi
            ON mb.machine_id = mi.machine_id
            LEFT JOIN dbo.branches b
            ON mi.branch_id = b.lookup_key
        WHERE 
            branch_code = 'MMMM'
/*
        GROUP BY 
            mb.machine_id,
            mi.branch_id,
            b.branch,
            b.branch_code
*/
        )x

    where x.rn=1

edited Jun 03 '14 at 09:09

answered Jun 03 '14 at 08:49

vhadalgi

7,027
6
38
67

I'm having an error to the partitioon line `Column 'dbo.machine_beat.date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` – naru Jun 03 '14 at 09:01
try adding `mb.date` in group by? – vhadalgi Jun 03 '14 at 09:02
@861051069712110711711710997114 No need to add `mb.date` in group by – Vignesh Kumar A Jun 03 '14 at 09:07
@861051069712110711711710997114 ooops sorry I added the `mb.date` in group by but the query result is the same as shown above (3 data) – naru Jun 03 '14 at 09:09
@VigneshKumar: yes i didnt see that! – vhadalgi Jun 03 '14 at 09:12
@861051069712110711711710997114 I'm getting the same result. Why did the `max()` deleted? – naru Jun 03 '14 at 09:19
@naru : you don't need to do that! since here u're using row_number() ovar(date desc) post in sqlfiddle with some sample – vhadalgi Jun 03 '14 at 09:23
@861051069712110711711710997114 I'll be back I'll try to replicate my table to sqlfiddle. Thanks in advance! – naru Jun 03 '14 at 10:11

score 1 · Accepted Answer · answered Jun 03 '14 at 12:34

@861051069712110711711710997114 is looking in the right direction - this is a greatest-n-per-group question. Yours is more complicated than the usual because the greatest portion is coming from a different table than the group portion. The only issue with his answer is that you hadn't provided sufficient information to finish it correctly.

The following solves the problem:

WITH Most_Recent_Beat AS (SELECT Machine.branch_id,
                                 Beat.machine_id, Beat.date,
                                 ROW_NUMBER() OVER(PARTITION BY Machine.branch_id 
                                                   ORDER BY Beat.date DESC) AS rn
                          FROM machine_id Machine
                          JOIN machine_beat Beat
                            ON Beat.machine_id = Machine.machine_id)
SELECT Beat.machine_id, Beat.date,
       Branches.lookup_key, Branches.branch, Branches.branch_code
FROM Branches
JOIN Most_Recent_Beat Beat
  ON Beat.branch_id = Branches.lookup_key
     AND Beat.rn = 1
ORDER BY Branches.branch, Beat.date DESC

(and corrected SQL Fiddle for testing. You shouldn't be using a different RDBMS for the example, especially as there were syntax errors for the db you say you're using.)

Which yields your expected results.

So what's going on here? The key is the ROW_NUMBER()-function line. This function itself simply generates a number series. The OVER(...) clause defines what's known as a window, over which the function will be run. PARTITION BY is akin to GROUP BY - every time a new group occurs (new Machine.branch_id value), the function restarts. The ORDER BY inside the parenthesis simply says that, per group, entries should have the given function run on entries in that order. So, the greatest date (most recent, assuming all dates are in the past) gets 1, the next 2, etc.
This is done in a CTE here (it could also be done as part of a subquery table-reference) because only the most recent date is required - where the generated row number is 1; as SQL Server doesn't allow you to put SELECT-clause aliases into the WHERE clause, it needs to be wrapped in another level to be able to reference it that way.

Thank you for the well detailed explanation, though I can't fully understand it as of now, but later, I'll try to analyze your answer step by step. Thanks also for correcting my `sqlfiddle`, it's my first time to use it. As of now, I can't up-vote your answer and @861051069712110711711710997114's answer, thanks heaps! it really help :) — naru, Jun 04 '14 at 01:41
ooops I have enough reputation all of the sudden, your answer and @861051069712110711711710997114's answer deserve an up-vote, once again thank you! — naru, Jun 04 '14 at 02:02
Hmm... if you can't understand it, I may need to modify it. What are you having trouble with? — Clockwork-Muse, Jun 04 '14 at 05:03
No,no, it's just that it's my first time to use the `partition by` thing and I got confuse how it work and how to use it. After reading your explanation again and again at the same time, executing your query step by step, I understand now what you said about the `CTE`and how it works. Thanks for the added knowledge and well explained answer :) — naru, Jun 04 '14 at 05:44

Select query with max date

2 Answers2