Distinct NAMES

Question

Post reply

Distinct NAMES

varunkum

Default port

Points: 1481
More actions
January 5, 2011 at 11:37 am

#231828

http://www.picscrazy.com/view/Image6V3
I wrote this sql to obtain the above data
select (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) ADDRESS, DISPLAY_NAME
from DSIPROC.TRB_VW_SUBS_INFO a
where (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) in
(select (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) from DSIPROC.TRB_VW_SUBS_INFO b
group by (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4), DISPLAY_NAME
having count(STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) >1 )
order by (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4)
In the image, i only want distinct DISPLAY_NAMES. How do i modify the code??? PLease help me. Thank you.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not sure what those double-pipes are meant to do. Is that T-SQL?

I tried it in a test database, and got a syntax error from it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Concatenation operator. Replace the || with +.

And then again, I might be wrong ...
David Webb

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Don't know what you mean by in the image...our network filter says it is a sex site.

for the query I don't quite understand why you need a subquery.

select DISPLAY_NAME, COUNT(DISPLAY_NAME)

from DSIPROC.TRB_VW_SUBS_INFO

group by DISPLAY_NAME

having COUNT(DISPLAY_NAME) > 1

This should get you a list of names that have more than 1 occurrence in the table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

varunkum Default port Points: 1481 More actions · Answer 4

Sean Lange (1/5/2011)
Don't know what you mean by in the image...our network filter says it is a sex site.
for the query I don't quite understand why you need a subquery.
select DISPLAY_NAME, COUNT(DISPLAY_NAME)
from DSIPROC.TRB_VW_SUBS_INFO
group by DISPLAY_NAME
having COUNT(DISPLAY_NAME) > 1
This should get you a list of names that have more than 1 occurrence in the table.

Its pics crazy website. Here is anew link

http://www.mediafire.com/?8u5tj3cjij2c4n6

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

add distinct to your query. simple as that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

varunkum Default port Points: 1481 More actions · Answer 6

Sean Lange (1/5/2011)
add distinct to your query. simple as that.

That didn't work.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

varunkum Default port Points: 1481 More actions · Answer 8

Sean Lange (1/5/2011)

I want distinct DISPLAY_NAME(as i mentioned in the question) not ADDRESS. Thank you.:-)

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

So don't select that column.

select distinct [whatever columns you want]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

varunkum Default port Points: 1481 More actions · Answer 10

Sean Lange (1/5/2011)
So don't select that column.
select distinct [whatever columns you want]

select (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) ADDRESS, distinct DISPLAY_NAME

from DSIPROC.TRB_VW_SUBS_INFO a

where (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) in

(select (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) from DSIPROC.TRB_VW_SUBS_INFO b

group by (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4), DISPLAY_NAME

having count(STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) >1 )

order by (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4)

I added distinct For DISPLAY_NAME and it gives me syntax error.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

You need to take out the address column if you don't want it.

select distinct name from ....

You just can't get a distinct column. You are trying to get distinct records.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/