QUERY TROUBLES - 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 2008 Forums

Transact-SQL (2008)

QUERY TROUBLES

Author

Topic

1seoadvies
Starting Member

2 Posts

Posted - 2011-03-18 : 10:49:58

I have tmp table with an id, (int) userid, (guid), photoid (int)

row 1: 4, 000, 1
row 2: 4, 000, 2
row 3: 8, 111, null
row 4: 9, 222, null
row 5: 10, 333, 1
row 6: 10, 333, 2
row 7: 12, 555, null

how do I get only the row(s) with the min(photoid) and also the row(s) with the null value?
I only want to see row 1,3,4,5 and 7

yes, I know about the uniqueidentifier ;-)

Any ideas??

http://www.1seoadvies.nl

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 11:06:36

DECLARE @TABLE TABLE(RowId int identity(1,1),ID int,UserID Char(3),PhotoID int)
INSERT INTO @TABLE
SELECT 4, '000', 1 UNION
SELECT 4, '000', 2 UNION
SELECT 8, '111', null UNION
SELECT 9, '222', null UNION
SELECT 10, '333', 1 UNION
SELECT 10, '333', 2 UNION
SELECT 12, '555', null

SELECT t1.RowId,t1.ID,t1.UserID,t2.photoID
FROM @TABLE t1
INNER JOIN
(

select t.ID,t.UserID
,MIN(ISNULL(PhotoID,0)) as PhotoID
from @TABLE t

GROUP BY t.ID,t.UserID
) t2
on t1.ID = t2.ID
and isnull(t1.PhotoID,0) = t2.photoid

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 11:13:47

SELECT t1.RowId,t1.ID,t1.UserID,t1.ID
FROM
(
SELECT RowId
,ID
,UserID
,PhotoID
,[rown] =ROW_NUMBER() over(partition by id,userid order by isnull(t1.photoid,0))
FROM @TABLE t1
)t1

WHERE t1.rown =1

jim

Everyday I learn something that somebody else already knew

1seoadvies
Starting Member

2 Posts

Posted - 2011-03-18 : 12:41:33

Thanks Jim

This helps

http://www.1seoadvies.nl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources