Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Angry Unanswered: Max with distinct two columns and corresponding third field

    Hi, i need to write a query and can't get it to work no matter how it try. Here's what i need:

    T1
    -------
    a1
    a2
    datetime
    a4
    a5
    .....

    i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me. Is there any way to do it?
    PS.
    for every specific a1 there is many entries of a2 + timedate etc.

    Code:
    T1
    -----------------------------
    a1   a2     timedate
    
    1    1       2014-04-31 10:59:38.000    ......
    1    2       2014-04-31 10:59:39.000 .......
    1    3       2014-04-31 10:59:39.500 .......
    2    1       timedate .......
    2    2       timedate .......etc

    select distinct t1.a1
    ,max (t1.a2)
    from t1
    group by blah

    thanks in advance
    Last edited by stsniper; 04-30-14 at 12:15.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       a            INT         NOT NULL
    ,  b            INT         NOT NULL
    ,  timedate     DATETIME    NOT NULL
       )
    
    INSERT INTO @t (a, b, timedate)
       VALUES
          (1, 1, '2014-04-30 10:59:38.000')
    ,     (1, 2, '2014-04-30 10:59:39.000')
    ,     (1, 3, '2014-04-30 10:59:39.500')
    ,     (2, 1, '2014-04-30 11:00:39.500')
    ,     (2, 2, '2014-04-30 12:23:39.500')
    
    ; WITH cte AS (
    SELECT a, b, timedate
    ,  Row_Number() OVER (ORDER BY CASE WHEN a < b THEN b ELSE a END DESC) AS rn
       FROM @t
    )
    SELECT a, b, timedate
       FROM cte
       WHERE  1 = rn
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2014
    Posts
    4
    remove please
    Last edited by stsniper; 04-30-14 at 14:43.

  4. #4
    Join Date
    Apr 2014
    Posts
    4
    Thank you for help but

    Code:
    comparing them is not really an option, since a=id and b=#of runs
    
    ID            #ofRuns               Date
    24                1                     date
    24                2                     date
    24                3                     date
    25                1                     date
    26                1                     date
    26                2                     date
    27                1                     date
    desired result
    Code:
    ID            #ofRuns               Date
    
    24                3                     date
    25                1                     date
    26                2                     date
    27                1                     date
    thank you

    PS. another thing for each latest run the date would be the latest, so there's a chance of using id and max date, but then how can i get that to work, with selecting two and needing three fields.
    Last edited by stsniper; 04-30-14 at 14:40.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The @t variable is only to hold your sample data... You can use the actual table name in place of it when you do this for class.
    Code:
    DECLARE @t TABLE (
       id           INT         NOT NULL
    ,  runs         INT         NOT NULL
    ,  timedate     DATETIME    NOT NULL
       )
    
    INSERT INTO @t (id, runs, timedate)
       VALUES
       (24, 1, '2001-01-01 01:01:01')
    ,  (24, 2, '2001-02-03 04:05:06')
    ,  (24, 3, '2001-04-05 07:08:09')
    ,  (25, 1, '2002-06-01 01:01:01')
    ,  (26, 1, '2003-07-10 01:01:01')
    ,  (26, 2, '2004-08-11 01:01:01')
    ,  (27, 1, '2005-09-12 01:01:01')
    
    ; WITH cte AS (
    SELECT id, runs, timedate
    ,  Row_Number() OVER (PARTITION BY id ORDER BY runs DESC) AS rn
       FROM @t
    )
    SELECT id, runs, timedate
       FROM cte
       WHERE  1 = rn
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2014
    Posts
    4
    thank you, that helped me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •