distinct select options - 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 select options

Author

Topic

wylderubicon
Starting Member

10 Posts

Posted - 2008-02-29 : 09:34:44

Hi, I am new to this forum.

I have a question regarding select queries.

I need to to a select on a distinct field but need to output more that that field.

so if I have

select name, company, address, location_number from table abc

the location_number is the only field that has to be one per on output.

table abc will have

Alan Andrews abc company 1 Main st 00001
John Andrews abc company 1 Main st 00001
bob Smith ZZX Inc 3 broadway 00001
eric Roberts SSS, LLC 123 Elm ST 00002
Rick Roberts SSS, LLC 123 Elm ST 00002
Jay Smith JJJ Inc 524 5th ave 00002

shold result to
Alan Andrews abc company 1 Main st 00001
eric Roberts SSS, LLC 123 Elm ST 00002

Is there a way to do this?

when I say:

select distinct name, company, address, location_number from table abc

I still get all the records back because its doing a distinct across all the fields I am selecting.

Thanks!

Alan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 09:38:06

[code]Select max([name]) as [name], max(company) as company, max(address) as address, location_number
from table
group by location_number[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

wylderubicon
Starting Member

10 Posts

Posted - 2008-02-29 : 10:22:29

thanks for the quick reply! and this worked!

I have one more question, but I will put in in a new thread.

wylderubicon
Starting Member

10 Posts

Posted - 2008-02-29 : 11:05:01

just noticed something using max.

its not returning the correct information. Its mixing information from all the fields and rows returning the max of all into one row
so its returning

rick roberts sss, llc 524 5th ave 0002

from

Rick Roberts SSS, LLC 123 Elm ST 00002
Jay Smith JJJ Inc 524 5th ave 00002

any way to return info from the correct row?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 11:09:26

How do you define "correct row"?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

wylderubicon
Starting Member

10 Posts

Posted - 2008-02-29 : 11:11:50

I need my results from a single row. Without mixing address, company, contact info.
because in the example above I get the greatest contact name, greates company name and greatest address. Which then becomes incorrect information for a mailing purpose.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 11:18:06

All information from same record, presumably.
MAX get it's data per group, not necesarily same record.

Try this

Select	[name],
	company,
	[address],
	location_number
from	(
		Select	[name],
			company,
			[address],
			location_number,
			row_number() over (partition by location_number order by company) AS recid
		from	table1
	) AS d
where	recid = 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources