Query to get Max/Min row details for multiple fields

Question

I have a table structure similar to the following example:

DateTime              V1    V2    V3    V4    
10/10/10 12:10:00     71    24    33    40
10/10/10 12:00:00     75    22    44    12
10/10/10 12:30:00     44    21    44    33
10/10/10 12:20:00     80    11    88    12

With DateTime field being the unqiue and key field, I want a query to output min and max date time for each values so that it will show something like below:

TYPE    MIN     MINDATETIME         MAX     MAXDATETIME 
V1      44      10/10/10 12:30:00   80      10/10/10 12:20:00 
V2      11      10/10/10 12:20:00   24      10/10/10 12:10:00 
V3      33      10/10/10 12:10:00   88      10/10/10 12:20:00 
V4      12      10/10/10 12:20:00   40      10/10/10 12:10:00

If there are multiple rows with the same min/max value, then it should get the latest one.

With Inner Join on a field, I know to get the details of min/max row for a field, but only way I can think getting everything in one query is to union them all. I think there might be a better solution. Any help is appreciated.

I am using SQL Server 2008.

Thanks.

Answer 1

You can get both max and min values in a single query. To get max/min datetime you will have to join the max/min query back to the table (once per every value)

something to the effect of:

select values.*, mint.timestamp, maxt.timestamp from
    (select MIN(value) as minv, MAX(value) as maxv from table group by value) values,
    table mint,
    table maxt
where 
    mint.value = values.minv
    maxt.value = values.maxv

Answer 2

Not sure if this is what you are after, but it seems to solve this basic case:

create table t(a int,b int);
insert into t(a,b) values(1,2),(2,3),(-1,-10);

select max(t1.a),min(t1.a),max(t2.b),min(t2.b) from t as t1,t as t2;
+-----------+-----------+-----------+-----------+
| max(t1.a) | min(t1.a) | max(t2.b) | min(t2.b) |
+-----------+-----------+-----------+-----------+
|         2 |        -1 |         3 |       -10 |
+-----------+-----------+-----------+-----------+

Answer 3

• Unpivot into useful rows ("normalise")
• Work out MIN/MAX per Type, allowing for MAX datetime if ties
• Extract MIN/MAX values

Hey presto...

DECLARE @foo TABLE (
    DateTimeKey datetime NOT NULL,
    V1 int NOT NULL,
    V2 int NOT NULL,
    V3 int NOT NULL,
    V4 int NOT NULL
);

INSERT @foo (DateTimeKey, V1, V2, V3, V4)
SELECT '10/10/10 12:10:00',     71,    24,    33,    40 
UNION ALL SELECT '10/10/10 12:00:00',     75,    22,    44,    12 
UNION ALL SELECT '10/10/10 12:30:00',     44,    21,    44,    33 
UNION ALL SELECT '10/10/10 12:20:00',     80,    11,    88,    12;


WITH cTE AS 
(
    SELECT
        [Type], [Value], DateTimeKey,
        ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY [Value], DateTimeKey DESC) AS TypeRankMin, 
        ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY [Value] DESC, DateTimeKey DESC) AS TypeRankMax
    FROM
        (
        SELECT
            [Type], [Value], DateTimeKey
        FROM 
           (SELECT DateTimeKey, V1, V2, V3, V4 FROM @foo) p
           UNPIVOT
           ([Value] FOR [Type] IN (V1, V2, V3, V4)) AS unp
        ) bar
)
SELECT
    Mn.[Type], [MIN], MINDATETIME, [MAX], MAXDATETIME
FROM
    (
        SELECT 
            [Type], [Value] AS [MIN], DateTimeKey AS MINDATETIME
        FROM
            cTE
        WHERE
            TypeRankMin = 1
    ) Mn
    JOIN
    (
        SELECT 
            [Type], [Value] AS [MAX], DateTimeKey AS MAXDATETIME
        FROM
            cTE
        WHERE
            TypeRankMax = 1
    ) Mx ON Mn.[Type] = Mx.[Type];

Answer 4

I'd start with normalizing your data. Since V2 does not depend on V1, they should not be in the same row. Here's a normalizing query:

SELECT DateTime, 'V1' as Type, V1 as Value FROM @data
UNION ALL select DateTime, 'V2', V2 FROM @data
UNION ALL select DateTime, 'V3', V3 FROM @data
UNION ALL select DateTime, 'V4', V4 FROM @data

With that, what remains is the "selecting records holding group-wise maximum with tie" problem. One way to solve that is:

;WITH normal AS (
    SELECT DateTime, 'V1' as Type, V1 as Value FROM @data
    UNION ALL select DateTime, 'V2', V2 FROM @data
    UNION ALL select DateTime, 'V3', V3 FROM @data
    UNION ALL select DateTime, 'V4', V4 FROM @data
)
SELECT *
FROM    (SELECT  DISTINCT Type FROM normal) dd
CROSS APPLY (
        SELECT   TOP 1 DateTime MINDATETIME, Value MIN
        FROM     normal di
        WHERE    di.Type = dd.Type
        ORDER BY Value, DateTime desc
        ) dimin
CROSS APPLY (
        SELECT   TOP 1 DateTime MAXDATETIME, Value MAX
        FROM     normal di
        WHERE    di.Type = dd.Type
        ORDER BY Value desc, DateTime desc
        ) dimax

The dd query returns the Types (V1, V2, ...) The first cross apply searches for the minimums for that type, and the second cross apply searches for the maximums.

Answer 5

i read the question and thought: i'd rather do that in php than sql.

then i read the answers and i thought: i'd really rather do that in php than sql.