complex max - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex max

Author

Topic

grab
Starting Member

1 Post

Posted - 2007-08-27 : 23:14:28

my table is something like

ID before after
3 1 2
4 2 3
5 3 4
6 4 5
7 4 6
8 4 7
9 7 8
10 8 9
11 7 10
12 7 11

i want a result like
ID before after
3 1 2
4 2 3
5 3 4
8 4 7
12 7 11

in words, i want unique max after for each of the unique before feilds

any suggestion for the sql statement for this?

thanks

jjg

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-27 : 23:41:13

Not sure why 10 8 9 is not in the result set.

DECLARE @T TABLE (ID int, Before int, After int)

INSERT INTO @T SELECT 3, 1, 2
UNION SELECT 4, 2, 3
UNION SELECT 5, 3, 4
UNION SELECT 6, 4, 5
UNION SELECT 7, 4, 6
UNION SELECT 8, 4, 7
UNION SELECT 9, 7, 8
UNION SELECT 10, 8, 9
UNION SELECT 11, 7, 10
UNION SELECT 12, 7, 11

SELECT A.ID, A.Before, A.After
FROM @T A
INNER JOIN
(
SELECT A.Before, After = MAX(A.After) FROM @T A GROUP BY A.Before
) B
ON B.Before = A.Before AND B.After = A.After
ORDER BY A.ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 01:06:03

[code]SELECT ID, Before, After
FROM (
SELECT ID, Before, After, ROW_NUMBER() OVER (PARTITION BY Before ORDER BY After DESC) AS RecID
FROM @T
) AS d
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2007-08-28 : 01:06:46

SELECT *
FROM @T T
WHERE ID IN (SELECT MAX(ID) FROM @T WHERE BEFORE = T.BEFORE)
ORDER BY 1

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-28 : 04:03:12

quote:
Originally posted by ranganath

SELECT *
FROM @T T
WHERE ID IN (SELECT MAX(ID) FROM @T WHERE BEFORE = T.BEFORE)
ORDER BY 1

MAX(ID)?

ranganath
Posting Yak Master

209 Posts

Posted - 2007-08-29 : 01:17:28

DECLARE @T TABLE (ID int, Before int, After int)

INSERT INTO @T SELECT 3, 1, 2
UNION SELECT 4, 2, 3
UNION SELECT 5, 3, 4
UNION SELECT 6, 4, 5
UNION SELECT 7, 4, 6
UNION SELECT 8, 4, 7
UNION SELECT 9, 7, 8
UNION SELECT 10, 8, 9
UNION SELECT 11, 7, 10
UNION SELECT 12, 7, 11

SELECT *
FROM @T T
WHERE ID IN (SELECT MAX(ID) FROM @T WHERE BEFORE = T.BEFORE)
ORDER BY 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources