selet distinct query help needed

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

General SQL Server Forums

New to SQL Server Programming

selet distinct query help needed

Author

Topic

sumit381
Starting Member

6 Posts

Posted - 2007-09-17 : 12:00:57

I have a select query that is as follows:

SELECT NAME,
sex,
job_class,
mob,
dob,
yob,
doe,
moe,
yoe,
py_salary,
cy_salary,
unused_sick_hrs,
dept
FROM active_participant_load
where ...;

I want this query to return only results where just the name column is distinct but when I put a distinct in front of name it will do a distinct on all columns. Any help would be appreciated in this matter. Thank you.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 12:16:20

What would you like to happen when there are duplicates

a. show both
b. show none
c. show first
d. show latest

???

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-17 : 12:23:47

Also, what version of SQL are you using? If 2005 you could try something like:

SELECT 
	NAME,
	sex,
	job_class,
	mob,
	dob,
	yob,
	doe,
	moe,
	yoe,
	py_salary,
	cy_salary,
	unused_sick_hrs,
	dept
FROM 
	(
		SELECT
			NAME
			sex,
			job_class,
			mob,
			dob,
			yob,
			doe,
			moe,
			yoe,
			py_salary,
			cy_salary,
			unused_sick_hrs,
			dept,
			ROW_NUMBER() OVER(PARTITION BY NAME) AS RowNumber
		FROM
			active_participant_load
		where ...;
	)
WHERE
	RowNumber = 1

sumit381
Starting Member

6 Posts

Posted - 2007-09-17 : 12:38:56

quote:
Originally posted by ValterBorges

What would you like to happen when there are duplicates

a. show both
b. show none
c. show first
d. show latest

???

I would like it to show the first one.

sumit381
Starting Member

6 Posts

Posted - 2007-09-17 : 12:40:13

quote:

Originally posted by Lamprey

Also, what version of SQL are you using? If 2005 you could try something like:

SELECT 
	NAME,
	sex,
	job_class,
	mob,
	dob,
	yob,
	doe,
	moe,
	yoe,
	py_salary,
	cy_salary,
	unused_sick_hrs,
	dept
FROM 
	(
		SELECT
			NAME
			sex,
			job_class,
			mob,
			dob,
			yob,
			doe,
			moe,
			yoe,
			py_salary,
			cy_salary,
			unused_sick_hrs,
			dept,
			ROW_NUMBER() OVER(PARTITION BY NAME) AS RowNumber
		FROM
			active_participant_load
		where ...;
	)
WHERE
	RowNumber = 1

I am not using 2005, so this would not work for me. Thank you so much for your effort and time though.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 12:41:17

Ok is there a field which tells you the date when the record was entered? How would you tell by looking at it that it was the first?

sumit381
Starting Member

6 Posts

Posted - 2007-09-17 : 13:13:56

Ok, the problem is solved. In my where clause I was look for name = '...'. Then I used name in ('...') and the problem was solved. Thanks to everyone for their help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources