SQL query with counting

kashwmu
kashwmu used Ask the Experts™
on
I have a table with tons of information about different parts. I need to pull out if that table the 20 latest inspections for each part. This seems simple to me but i cant seem to figure that out. Any Help is Appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
dwe761Software Engineer

Commented:
Could you provide a bit more info?  Are there multiple fields containing inspection dates or is there one row for each inspection?  It would help if you'd provide some sample data and your desired outcome so we may more effectively help you.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps:

select inspections
from ur_table
group by inspections
having count(*) > 20
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
kashwmu,

I would agree with dwe761 that a little more information is needed like the table structure.  Although, the way the question is phrased led me to believe you had a row for each inspection as this reminds me of needing to pull top 10 customers for a sales rep.  Maybe I am incorrect, but that was what I associated your request to.

Given this is SQL Server 2005 and my intepretation, I would suggest using ROW_NUMBER() function with OVER analytical clause to accomplish this.

In the code snippet is a general example that you will need to change per your actual table structure.

Notes:
+The PARTITION BY is the grouping that makes the row unique.  I went with a single column for part identifier called ItemID.
+ORDER BY should be date field or other data that establishes latest.  Normally, this is sort by date of inspection (addition of record to table) descending which puts the latest values at the top.

Hopefully this helps.

Respectfully yours,

mwvisa1
SELECT *
FROM (
SELECT ItemID
, {column list}
, ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY InspectionDate DESC) AS row
FROM Items
) t
WHERE row BETWEEN 1 AND 20

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
OK i will try and be more specific and see if this will make it clearer. I have a table that has inspection results. The table is designeg to have one row per inspection that has info like part weight which i am tyring to find. So the information would be RowID, part, weight. I need to pull the last 20 results for each part. So if i have 2 parts A, B. I would have 40 results(20 for A and 20 for B).
Chief Technology Officer
Most Valuable Expert 2011
Commented:
So higher RowID equals latest inspection?
SELECT RowID, part, weight
FROM (
SELECT RowID, part, weight
, ROW_NUMBER() OVER (PARTITION BY part ORDER BY RowID DESC) AS rank
FROM Items
) t
WHERE rank BETWEEN 1 AND 20

Open in new window

awking00Information Technology Specialist

Commented:
What defines the last 20 results? If there some sort of date field in the record or is it some sort of automatically incremented field that gets populated upon insert? If it's the latter, and that field is RowID, then mwvisal has given you the answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial