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

distinct query

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-01-06 : 14:54:56

I am trying to select distinct first names and last names from my table, there are multiple rows that are the same. Is this an accurate query for this?

Select distinct a.fname, a.lname,  a.email, a.phone 
from Quitline_ScheduleCall a
order by a.lname

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 14:58:22

Select * from
(Select Row number()over(partition by fname,lname order by lname)as seq,* from table)t
Where t.seq =1

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-01-06 : 15:03:07

I receive a error from that query. "Line 3 Incorrect syntax near '('.
"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 15:05:14

What is the database compatibility level of the database that you are running this query? It'll need to be 90 or higher.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-01-06 : 15:09:37

I'm using sql server 2005

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 15:12:07

quote:
Originally posted by sodeep

Select column names from
(Select Row number()over(partition by fname,lname order by lname)as seq,* from table)t
Where t.seq =1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 15:12:41

But what is the database compatibility level of this database?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-01-06 : 15:26:27

I check it is a 90+

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 15:58:07

Can you post exact query you used?

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-01-06 : 17:28:08

Select * from
(Select Row number()over(partition by fname,lname order by lname)as seq,* from Quitline_ScheduleCall)t
Where t.seq =1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 22:56:13

quote:
Originally posted by jgonzalez14

Select * from
(Select Row_number()over(partition by fname,lname order by lname)as seq,* from Quitline_ScheduleCall)t
Where t.seq =1

I thougt that ur query has an error, becoz row_number() is correct syntax

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-07 : 00:26:53

It should work if you are using SQL 2005(90) or above.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources