Challenged by selecting Top 1 in a Group

Question

Post reply

Challenged by selecting Top 1 in a Group

rdavid-773903

Grasshopper

Points: 22
More actions
March 22, 2010 at 6:27 am

#220177

This should be easier than I am finding it.
I have a table of Printer Life Time Page Counts (LTPCs) for a number of printers. LTPCs are the number of pages a printer has printed in its life for A4 mono, A3 Mono, A4 colour and A3 Colour.
The unique key is the PrinterLTPCID. The parent key is PrinterID.
The data is made up of a count type (may be manual, electronic etc), the count date-and-time and the 4 different LTPCs.
I need to extract the latest page count based on the most recent date and find the matching 4 LTPCs.
Simple enough, except for a couple of peculiarities of the printer industry.
These peculiarities include: (1) Swapping the mother board on a printer, in which case the LTPCs drop back to zero and (2) the fact that electronic LTPC sometimes capture the mono A4 LTPC but fail on the Colour A3 count, in which case a count of 0 is returned for the A3 colour.
I need to get all data from a single row, which is the one with the latest date.
I have tried the following approaches:
1) A simple query grouping by PrinterID and taking the top (1) row in descending date order is a sensible place to start. One can then use Max (or max () over(Partition by..) to get the LTPCs. However, I don't want the maximum LTPCs for the reasons listed under peculiarities above. The four LTPCs must be a matched set and must come from the latest dated row.
2) Writing sub queries to start to process by getting me down to a single row per PrinterID. Here I run into the problem that if I group by PrinterID with Max(Date) I don't have the key of the PrinterLTPCID. If I add PrinterLTPCID into the grouping, I no longer group as it is the primary key. i.e. the latest date may not have the highest PrinterLTPCID.
As you can see, by now I have got myself very muddled and there is probably a really easy approach to this problem. The problem is basically grouping by one column, selecting the top(1) or max based on another column and knowing the primary key of the row selected.
Help would be greatly appreciated.

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Lookup Row_Number() in BOL:

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Combining this with a CTE should get you what you need.

If you post some sample data and the expected result, I'm sure someone can post a working example for you:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

HowardW (3/22/2010)
Lookup Row_Number() in BOL:
http://msdn.microsoft.com/en-us/library/ms186734.aspx
Combining this with a CTE should get you what you need.

Agreed

If you post some sample data and the expected result, I'm sure someone can post a working example for you:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Absolutely agreed. We can provide better answers with better detail (sample data, table structures, etc)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 3

Use APPLY with TOP. If you provide easily consumable test data, I will write a demonstration script.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

rdavid-773903 Grasshopper Points: 22 More actions · Answer 4

Thanks to all three who responded, where the common theme was providing some easy to use data for you to look at.

Well, as so often happens, a combination of the clues in your post (Row_Number), simplification for the test data and me trying to prepare some example of what was not working for me, got me to the solution.

It may have one level too many, but this solution works for me:

Select s.*, #Printers.SerialNo From (select PrinterID, LTPCDate, LTPCA4Mono, LTPCA4Colour from(

select PrinterID,LTPCDate,LTPCA4Mono,LTPCA4Colour, row_number()over(partition by PrinterID order by LTPCDate desc) rn

from #PrinterLTPCs) t

where rn=1) s Inner join #Printers on #Printers.PrinterID = s.PrinterID

I can now plug this into a large query.

Thanks again for the help.

Paul White SSC Guru Points: 150468 More actions · Answer 5

APPLY would have been much cooler :laugh:

Glad you found the solution!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

rdavid-773903 (3/23/2010)
Thanks to all three who responded, where the common theme was providing some easy to use data for you to look at.
Well, as so often happens, a combination of the clues in your post (Row_Number), simplification for the test data and me trying to prepare some example of what was not working for me, got me to the solution.
It may have one level too many, but this solution works for me:
Select s.*, #Printers.SerialNo From (select PrinterID, LTPCDate, LTPCA4Mono, LTPCA4Colour from(
select PrinterID,LTPCDate,LTPCA4Mono,LTPCA4Colour, row_number()over(partition by PrinterID order by LTPCDate desc) rn
from #PrinterLTPCs) t
where rn=1) s Inner join #Printers on #Printers.PrinterID = s.PrinterID
I can now plug this into a large query.
Thanks again for the help.

You're welcome and congrats on getting it working.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events