|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
August 25th, 2004, 07:31 AM
|
|||
|
|||
|
Calculating Median in SQL
Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this; ServerName | ExecTime ----------------------- server1 | 0.07 server2 | 0.17 server1 | 0.27 server1 | 0.37 server2 | 0.47 server1 | 0.57 server1 | 0.67 server2 | 0.77 My SQL below gives me ServerName | ExecTime ----------------------- server1 | 0.37 Where as i want ServerName | ExecTime ----------------------- server1 | 0.37 server2 | 0.47 Here is my SQL, hope someone can modify it and thanks in advance. Code:
SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)
|
|
#2
August 25th, 2004, 10:49 AM
|
||||
|
||||
|
you wish to convert this to mysql syntax?
are you on 4.1 which allows subqueries?
|
|
#3
August 25th, 2004, 10:52 AM
|
|||
|
|||
|
r937,
thanks for the reply, no i dont wish to convert it to mysql. I wanted someobdy to help me modify the query so that it selected all median 'execution times' for every instance in my table and not just the median execution time for all records, if you take a look at my original post it should make more sense. Thanks for your help.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Calculating Median in SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
