return minimum records

Question

Post reply

return minimum records

Nomvula

Hall of Fame

Points: 3775
More actions
August 23, 2016 at 4:07 am

#329667

Hi
I have the following table
CREATE TABLE TempTable(
RowID INT,
AssetCode VARCHAR(50) NULL,
WorkOrder VARCHAR(10) NULL,
RequiredByDate DATETIME,
TimeDown TIME,
ReportText varchar(100))
INSERT INTO TempTable (RowId,AssetCode,WorkOrder,RequiredByDate,TimeDown,ReportText) VALUES (1000,'LH5635','S15214','2016-08-27 02:00:00.000','11:39','Weekly Service')
INSERT INTO TempTable (RowId,AssetCode,WorkOrder,RequiredByDate,TimeDown,ReportText) VALUES (1002,'LH5766','S15201','2016-08-26 02:00:00.000','11:37','Task Proposed')
INSERT INTO TempTable (RowId,AssetCode,WorkOrder,RequiredByDate,TimeDown,ReportText) VALUES (1003,'LH5766','R08595','2016-08-31 09:02:43.640','10:22','Weekly Electrical')
INSERT INTO TempTable (RowId,AssetCode,WorkOrder,RequiredByDate,TimeDown,ReportText) VALUES (1004,'DS7180','S15161','2016-08-24 02:00:00.000','11:31','Reflective Tape')
INSERT INTO TempTable (RowId,AssetCode,WorkOrder,RequiredByDate,TimeDown,ReportText) VALUES (1005,'DS7180','S15162','2016-08-24 02:00:00.000','11:31','Inspection')
INSERT INTO TempTable (RowId,AssetCode,WorkOrder,RequiredByDate,TimeDown,ReportText) VALUES (1006,'DS7296','R00040','2016-08-31 09:11:03.167','02:00','Engine Replacement')
INSERT INTO TempTable (RowId,AssetCode,WorkOrder,RequiredByDate,TimeDown,ReportText) VALUES (1007,'LB5180',NULL,NULL,NULL,NULL)
I need to return minimum records based on asset Code,
my result should be rows 1000,1002,1004,1006,1007

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT RowId, AssetCode, WorkOrder, RequiredByDate, TimeDown, ReportText

FROM (

SELECT RowId, AssetCode, WorkOrder, RequiredByDate, TimeDown, ReportText,

rn = ROW_NUMBER() OVER(PARTITION BY AssetCode ORDER BY RowID)

FROM #TempTable

) d

WHERE rn = 1

ORDER BY RowID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Nomvula Hall of Fame Points: 3775 More actions · Answer 2

thank you for the quick response,

my question is now, I technically I don't have ROWId in my table, I've it there In the example so I can distinguish easily which rows I need to return.

If you remove rowId completely from the table, how should it be?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Nomvula (8/23/2016)
thank you for the quick response,
my question is now, I technically I don't have ROWId in my table, I've it there In the example so I can distinguish easily which rows I need to return.
If you remove rowId completely from the table, how should it be?

for clarification....what rule are you intending to use for why you want 1004 and not 1005 ?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Nomvula Hall of Fame Points: 3775 More actions · Answer 4

what I want is the record with the minimum date per asset, in the case of 1004 and 1005 the date is the same and 1004 is the minimum record, that I can distinguish by workorder it's minimum

if the date is the same then I need minimum workorder.

I hope it's clear

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Nomvula (8/23/2016)
what I want is the record with the minimum date per asset, in the case of 1004 and 1005 the date is the same and 1004 is the minimum record, that I can distinguish by workorder it's minimum
if the date is the same then I need minimum workorder.
I hope it's clear

then alter the row_number function to order by requiredbydate and work order

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Nomvula Hall of Fame Points: 3775 More actions · Answer 6

Hi guys

I managed to sort it out, I used the query Chris gave me.

Thanks a million!!!