Query - 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)

Query

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2008-04-28 : 09:10:15

Hi all

To simplify, I made sample query below. I would like to have the result in such away that is grouping by ID1 and pick the latest of ID2 as below:

1, 125, 'TEST'
1, 125, 'TEST2'
2, 127, 'TEST'
2, 127, 'TEST2'

create table #temp1
(
	ID1 INT,
	ID2 INT,
	NAME VARCHAR(50)
)

INSERT INTO #temp1
SELECT 1, 124, 'TEST'
INSERT INTO #temp1
SELECT 1, 124, 'TEST2'
INSERT INTO #temp1
SELECT 1, 125, 'TEST'
INSERT INTO #temp1
SELECT 1, 125, 'TEST2'
INSERT INTO #temp1
SELECT 2, 126, 'TEST'
INSERT INTO #temp1
SELECT 2, 126, 'TEST2'
INSERT INTO #temp1
SELECT 2, 127, 'TEST'
INSERT INTO #temp1
SELECT 2, 127, 'TEST2'

SELECT  ID1, ID2, NAME
FROM #temp1
GROUP BY ID1

DROP TABLE #temp1

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-28 : 09:13:59

so in your business logic, what dictates what the 'latest' for ID2 is? is there a datetime column etc?

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 09:17:34

SELECT t1.* FROM #temp1 t1 inner join
(SELECT ID1, max(ID2) as ID2
FROM #temp1
GROUP BY ID1
) as t2 on t1.id1=t2.id1 and t1.id2=t2.id2
order by t1.id1,t1.id2

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 09:19:44

Make use of ROW_NUMBER() function.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:11:54

quote:
Originally posted by dewacorp.alliances

Hi all

To simplify, I made sample query below. I would like to have the result in such away that is grouping by ID1 and pick the latest of ID2 as below:

1, 125, 'TEST'
1, 125, 'TEST2'
2, 127, 'TEST'
2, 127, 'TEST2'

create table #temp1
(
ID1 INT,
ID2 INT,
NAME VARCHAR(50)
)

INSERT INTO #temp1
SELECT 1, 124, 'TEST'
INSERT INTO #temp1
SELECT 1, 124, 'TEST2'
INSERT INTO #temp1
SELECT 1, 125, 'TEST'
INSERT INTO #temp1
SELECT 1, 125, 'TEST2'
INSERT INTO #temp1
SELECT 2, 126, 'TEST'
INSERT INTO #temp1
SELECT 2, 126, 'TEST2'
INSERT INTO #temp1
SELECT 2, 127, 'TEST'
INSERT INTO #temp1
SELECT 2, 127, 'TEST2'

SELECT ID1, ID2, NAME
FROM #temp1
GROUP BY ID1

DROP TABLE #temp1

Did you try Madhi's solution? Why do you think it wont work for you?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources