# Thread: Max with distinct two columns and corresponding third field

1. Registered User
Join Date
Apr 2014
Posts
4

## 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

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

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

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

4. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

6. Registered User
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
•