Skip to main content

TOP n of Each -- SQL

Suppose you have a table like this and want to know say the Latest 5 [INPUT] values for all [INST]
ie; you have to sort a given INST by date (descending), take the 5 top most from the result. Repeat for each INST and merge all results together.
Pretty easy if you have a FOR EACH or some other type of LOOP.

[DW_X]
ID PROP INST INPUT UPDATEDATE USERID DOMAIN
12 4 1 KUJU 7/14/2009 11:27 1 1
13 4 1 KUJU2 7/14/2009 11:28 1 1
14 4 1 Kuju 7/14/2009 13:41 1 1
15 4 1 kuju2 7/15/2009 9:48 1 1
16 4 1 Kuju3 7/15/2009 13:47 1 1
17 4 6 litlOne 7/15/2009 15:11 1 1
21 4 7 Kuju no 7/16/2009 9:59 1 1
22 4 7 Test1 7/16/2009 13:08 1 1
23 4 7 test2 7/16/2009 13:09 1 1
24 4 1 B 5/16/2009 13:09 NULL 0
25 4 1 C 5/16/2009 13:09 1 0
26 4 1 AA 5/16/2009 13:09 1 0
27 4 1 WW 5/16/2009 13:09 1 0
28 4 1 RR 3/16/2009 13:09 1 0

Instead of loops, you can achieve this by a single SQL statement.
Was trying and it works out like this (MS SQL syntax)

SELECT * FROM DW_X T1 WHERE
T1.ID IN
(SELECT TOP 5
T2.ID FROM DW_X T2 WHERE INST= T1.INST ORDER BY UPDATE_DATE DESC )


In practice, you might not even want to hard code the number 5.
In my case, I stored the value in another table.
This gives you the flexibility of getting the top n for each INST, where the 'n' can vary for each INST.

SELECT * FROM DW_X T1 WHERE
T1.ID IN
(SELECT TOP (SELECT Hist_Count FROM IDE_INST WHERE IDE_INST.Id=T1.INST)
T2.ID FROM DW_X T2 WHERE INST= T1.INST ORDER BY UPDATE_DATE DESC )

Comments

Post a Comment

Popular posts from this blog

Siemens Washing Machine : Kill the Buzzer

The washing machine we have has the habit of sending out an irritating beep after it finishes the wash cycle. Ok enough; but these intermittent beeps go on and on and on till it is sure everyone came back home after your attending your funeral. One awful engineer who programmed the chip. Anyway, been looking around the net if there is a work around. Found this written by some Russian. I didn’t have patience to correct the grammar entirely. If the below does  not work, there is always the option of an Axe and ‘hey Siemens,..Heeeere is Johnyyy!) -- text You can change the volume of the buzzer according to your requirement. The operation procedure: 1. Switch on the machine,turn the program selector to Off . 2. Turn the program selector to cold Easy-care , press the additional function button Intensive stains and dont let go. You can hear the volume of the buzzer from minimum to maximum to off cycled (I didnt hear this!). If you decide the volume that yo...
3 comments
Read more

.Net Serialize and Deserialize Objects to XML and database

Serialization Create an empty instance of your object Dim Obj as New MYOBJECT Dim cn As New SqlConnection(ConnectionString.Text_) Dim cmd As New SqlCommand Dim Trans As SqlTransaction   With cmd                 .Connection = cn                 .Transaction = Trans                 .CommandType = CommandType.StoredProcedure                 .CommandText = "somestoredprocedure"             Dim xMLSerialiser_ As New System.Xml.Serialization.XmlSerializer ( Obj.GetType )                 Dim sWriter As New System.IO.StringWrit...
Post a Comment
Read more

SQL Server - Get columns and data types from all tables

Useful script USE SmartHire GO  SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID, DATA_TYPE  FROM SmartHire.INFORMATION_SCHEMA.COLUMNS  GO   Output TABLE_NAME COLUMN_NAME COLUMN_ID DATA_TYPE TblBankDetails BankID 1 int TblBankDetails BankName 2 varchar TblContactPreferences ContactPreferenceID 1 int
Post a Comment
Read more