Solved

SQL Help -

Posted on 2016-11-22
12
74 Views
Last Modified: 2016-12-28
I have a table of equipment, say just ID column and description.  Then a related table of work orders  - ID column, description, date of work, and FK to equipment.   One to many.     I need a query that pulls the 5 most recent work orders by date for each equipment record.
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 41898000
It's a simple JOIN:

SELECT TOP 5 *
FROM WorkOrders WO 
    INNER JOIN Equipment E ON E.ID = WO.Equipment ID
ORDER BY WO.WorkDate DESC;

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898002
Wont that just give me the top 5 across ALL equipment record?   I want the top 5 per unique equipment record.
0
 
LVL 34

Expert Comment

by:ste5an
ID: 41898022
Ah, didn't read that part.. What SQL Server version?

WITH Ordered AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY E.ID ORDER BY WO.WorkDate DESC) AS RN
    FROM WorkOrders WO 
        INNER JOIN Equipment E ON E.ID = WO.Equipment ID
    )
    SELECT *
    FROM Ordered
    WHERE RN < 6;

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898025
I know how to do this using a Stored Proc.   I could loop through all equipment records and do a query to pull the TOP 5 work order records by date, save them to a temp table, them return that temp table.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898026
2008
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898030
Does 2008 support that syntax?
0
 
LVL 34

Expert Comment

by:ste5an
ID: 41898032
Then it will work. ROW_NUMBER is part of T-SQL since SQL Server 2008.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41898037
Below is my real SQL.  I remove the date column in ORDER BY because that's actually in a related table - I need to add another JOIN.  I'm getting this error:

The column 'date_last_updated' was specified multiple times for 'Ordered'.



WITH Ordered AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY E.dep_ID ORDER BY WO.id DESC) AS RN
    FROM Work_Order WO
        INNER JOIN deprec E ON E.dep_ID = WO.equipment_id
    )
    SELECT *
    FROM Ordered
    WHERE RN < 6;
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41898132
I need to add another JOIN.  I'm getting this error:

The column 'date_last_updated' was specified multiple times for 'Ordered'.

You MUST use either the tablename or a table alias when specifying any column. This error is due to the fact that, now you have joined multiple tables, that at least 2 of those tables contain [date_last_updated] as columns. So you need to refer to a.[date_last_updated] or b.[date_last_updated] where a & b refer to your tables or aliases. You should use this style of referencing throughout the query - no exceptions.

Never use "select *" in stored procedures or views. We use it in our answers as a shorthand for: "we don't know the details of your tables, so you have to fill in this part". i.e. just because we use it in answers does not mean we recommend it for production use.

I'm afraid you are missing the point about ROW_NUMBER() and OVER(). It is absolutely ESSENTIAL that you use ORDER BY  a date/time column inside the OVER clause. To meet your stated requirement this simply isn't optional.  ROW_NUMBER() just generates unique integers but it does this according to the rules as specified through the OVER clause. The PARTITION BY is the grouping of data and the ORDER BY inside the OVER arranges the rows withing the partition so you know which row will get a value of 1,2,3,4,5 etc. Here we NEED to order by a date in DESCending order to get the "the 5 most recent work orders by date for each equipment record".

Also, I wish folks wouldn't (ab)use common tables expressions when there is zero advantage in using them. There is no reason to use "with ... as ()" in this query listed on this page so I suggest you don't use that approach. A simple "derived table" achieves the objective here.

Lastly you state you objective as "the 5 most recent work orders by date for each equipment record"

So: don't you need to list ALL items of equipment? and then only list UP TO 5 work orders (arranged by date descending). Imagine if an item of equipment has not been used on any work order. I think you need to reverse the precedence of the tables and use the equipment table in the FROM clause with a LEFT JOIN to work orders.

[please remember I am not recommending "select *"  you have to provide the details]
SELECT
      d.dep_id, d.last_updated_date
FROM (
      SELECT
            E.dep_ID
          , WO.last_updated_date 
          -- specify the other columns you need
          , ROW_NUMBER() OVER (PARTITION BY E.dep_ID ORDER BY WO.last_updated_date DESC) AS RN
      FROM deprec E 
      LEFT JOIN Work_Order WO ON E.dep_ID = WO.equipment_id
      ) d
WHERE d.RN < 6

Open in new window

0
 
LVL 34

Expert Comment

by:ste5an
ID: 41898160
Also, I wish folks wouldn't (ab)use common tables expressions when there is zero advantage in using them.

Well, there is an obvious advantage: It increases readability. And a CTE in this case creates the same execution plan, so no disadvantages. And last but least: Creating optimal SQL statements is query optimizing and and DBA task. The question here is clearly a development issues. I strongly encourage to separate those two roles in SQL development and implementation. This avoids premature optimization, which we know is the root of all evil..
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41898187
Readability is subjective.

If you approach sql as a procedural language then subjectively you improve readability. But SQL is not procedural.

There is zero advantage to using a cte here.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41906277
Thanks!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question