Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

SQL Help -

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
HLRosenberger
Asked:
HLRosenberger
  • 6
  • 4
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
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
 
HLRosenbergerAuthor Commented:
Wont that just give me the top 5 across ALL equipment record?   I want the top 5 per unique equipment record.
0
 
ste5anSenior DeveloperCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
HLRosenbergerAuthor Commented:
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
 
HLRosenbergerAuthor Commented:
2008
0
 
HLRosenbergerAuthor Commented:
Does 2008 support that syntax?
0
 
ste5anSenior DeveloperCommented:
Then it will work. ROW_NUMBER is part of T-SQL since SQL Server 2008.
0
 
HLRosenbergerAuthor Commented:
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
 
PortletPaulCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
PortletPaulCommented:
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
 
HLRosenbergerAuthor Commented:
Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now