General SQL Query - Distinct or ...??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Query - Distinct or ...???

Author

Topic

ramone_johnny
Starting Member

35 Posts

Posted - 2009-06-24 : 02:13:28

Hey guys,
Im trying to pull information from a DB, and I am unsure as to how best to write this query. Im not the best when it comes to SQL.

Heres the details ...

database - sql server 2000
table name - tblnew_cities

column names

pcode_postcode - int
pcode_area - nvarchar
pcode_state - nvarchar
pcode_ID - UID
pcode_stateID - int
pcode_city - nvarchar
pcode_cityID - int
pcode_areaID - int

In english heres what I need.

I need each distinct pcode_areaID, and pcode_area, as well as the pcode_postcode.

So as an example...

Yamanto as a suburb would be

pcode_postcode - 4305
pcode_area - Yamanto
pcode_areaID - 15317

Now the problem I am faced with is that many suburbs share the SAME postcode, so using distinct as i know it, doesnt work.

An example of this would be

Yamanto and Raceview

pcode_postcode - 4305
pcode_area - Yamanto
pcode_areaID - 15317

pcode_postcode - 4305
pcode_area - Raceview
pcode_areaID - 11388

Would someone be able to help me write this query?

Thanks,
John

Complete newbie. Please forgive me for having to ask such stupid questions...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 02:56:51

What is the expected output from this sample data?

Yamanto and Raceview

pcode_postcode - 4305
pcode_area - Yamanto
pcode_areaID - 15317

pcode_postcode - 4305
pcode_area - Raceview
pcode_areaID - 11388

E 12°55'05.63"
N 56°04'39.26"

ramone_johnny
Starting Member

35 Posts

Posted - 2009-06-24 : 03:10:44

Errr...I guess I would need them to be grouped by postcode?

pcode_area - Yamanto
pcode_areaID - 15317

pcode_area - Raceview
pcode_areaID - 11388

pcode_postcode - 4305

Complete newbie. Please forgive me for having to ask such stupid questions...

ramone_johnny
Starting Member

35 Posts

Posted - 2009-06-24 : 03:58:27

Anyone have any suggestions ???

Complete newbie. Please forgive me for having to ask such stupid questions...

ramone_johnny
Starting Member

35 Posts

Posted - 2009-06-24 : 08:18:28

Could someone please offer some assistance with this sql query?

Complete newbie. Please forgive me for having to ask such stupid questions...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 09:08:46

Read this blgo post VERY carefully and make sure you understand what he writes about
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources