Please help me select distinct rows

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)

Please help me select distinct rows

Author

Topic

salman4u
Starting Member

2 Posts

Posted - 2009-11-30 : 00:23:30

Hello,
say suppose sample data is
userid text date
1 m 1/1/2009
2 n 2/2/2009
1 p 3/2/2009
3 r 4/2/2008
2 l 5/2/2007

i want that userid should be distinct so the result should be either

userid text date
1 m 1/1/2009
2 n 2/2/2009
3 r 4/2/2008

or

userid text date
1 p 3/2/2009
3 r 4/2/2008
2 l 5/2/2007

means the userid should be distinct in resultset. How can i do this?
Thanks in advance!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-30 : 00:31:47

select userid,text,date from
(Select row_number()over(partition by userid order by userid)as rid,* from tablename)s
where rid = 1

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-30 : 00:31:47

Hi

You want this...


CREATE TABLE #TEMP(ID INT, TEXT VARCHAR(1), DATE DATETIME)

INSERT INTO #TEMP
SELECT 1,'M', '1/1/2009' UNION ALL
SELECT 2,'N', '2/2/2009' UNION ALL
SELECT 1,'P', '3/2/2009' UNION ALL
SELECT 3,'R', '4/2/2008' UNION ALL
SELECT 2,'L', '5/2/2007'


SELECT ID, MIN(TEXT)AS TEXT,MIN(DATE) AS DATE 
FROM #TEMP
GROUP BY ID


SELECT ID, MAX(TEXT)AS TEXT,MAX(DATE) AS DATE 
FROM #TEMP
GROUP BY ID

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources