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...
2 comments
Read more

HTML Sidebar menu without jQuery

Most examples I found on the net for a responsive sidebar had a reference to jQuery. For two lines of code, I find it an unnecessary overhead. So this is a basic page with no dependencies that has a toggle-able sidebar as well as a media query javascript that hides the sidebar below a breakpoint screen width. Modify as necessary. Maybe tricks could be added such as the one to change the hamburger to X when the sidebar is visible. https://jsbin.com/romiqov/2/edit?html,output <!DOCTYPE html> <html lang="en"> <head>     <meta charset="UTF-8">     <title>SideBar test</title>     <style>         * {             margin: 0;             padding: 0;             font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;         }         #sidebar { ...
Post a Comment
Read more

Sudoku Puzzle Generator

Sudoku puzzles can be generated by a switching the rows and columns of a valid puzzle. The switch has to be done between the 123, 456 and 789 rows/columns. ie; 1 cannot be switched with 7 for example. A random pair can be generated and if a loop is run say 50 times, we get a new puzzle. Then the cells can be hidden again randomly. The following code generates the puzzle from a base character string which is converted to a 81 length Char array. The output is the solution as well as the puzzle with blank (or 0s)               Dim stdArray As String = "317849265245736891869512473456398712732164958981257634174925386693481527528673149"         Dim charArray() As Char = stdArray.ToCharArray         Dim rng As New Random         Dim m As Integer         Dim row1 As Integer         Dim row2 As Integer         Dim col1...
Post a Comment
Read more