Re: Select statement - 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)

Re: Select statement

Author

Topic

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-23 : 09:28:38

Hallo,

I have the following select query which extracts the data I want;

SELECT DISTINCT PRACTICE, POSTCODE
FROM dbo.GP27

This brings all the distinct practices and post code from table dbo.GP27, which totals 9040 records.

Now, I want to write another select statemnt to bring the extra bits such as address1, address2, address3, telephone for the 9040 records.

Therefore when I write the following query it brings 17000 records

SELECT TOP (100) PERCENT dbo.QryPracPostCodeDist.PRACTICE, dbo.QryPracPostCodeDist.POSTCODE, dbo.GP27.ADDRESS1, dbo.GP27.ADDRESS2, dbo.GP27.ADDRESS3, dbo.GP27.TELEPHONE1
FROM dbo.QryPracPostCodeDist INNER JOIN
dbo.GP27 ON dbo.QryPracPostCodeDist.PRACTICE = dbo.GP27.PRACTICE AND dbo.QryPracPostCodeDist.POSTCODE = dbo.GP27.POSTCODE
ORDER BY dbo.QryPracPostCodeDist.PRACTICE

Please can anyone advise me how I can re-write the query to bring ONLY the 9040 records with ethe extra bits.

Thanks

pootle_flump

1064 Posts

Posted - 2009-04-23 : 09:31:29

You have multiple address lines for each practice. So you need to tell us how you identify which one you want.

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-23 : 09:39:39

i would like to write the first address lines for each practice, ignoring the second one however, if the first lines are NULL then the second one should be displayed..

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 09:54:17

Ok then, please define what "first" means to you.
Remember we don't have access to your data.

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

pootle_flump

1064 Posts

Posted - 2009-04-23 : 10:15:14

Also bear in mind that there is no order to rows without you defining one based on column(s) values. If you really mean the first that appears in the table result that you get then in effect this means, to us, "get me any arbitrary row". This is a fundamental rule of relational databases - there is no inherent order to rows in tables.

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-23 : 10:26:04

addresses that appear first in each column for that respective practice

pootle_flump

1064 Posts

Posted - 2009-04-23 : 10:30:22

Note that to us that means "just get me one - any, I don't care". What appears "first" when you view your table is just a fluke unless you use an order by clause.

jamilahmed
Starting Member

18 Posts

Posted - 2009-04-23 : 10:44:22

Ok give me any address as long as it is not Null

pootle_flump

1064 Posts

Posted - 2009-04-23 : 10:51:16

[code]DECLARE @t TABLE
(
practice VARCHAR(100)
, add1 VARCHAR(100)
)

INSERT @t
SELECT 'this', 'a'
UNION ALL
SELECT 'that', 'b'

SELECT *
FROM --any address
(
SELECT *
, rn = ROW_NUMBER() OVER (ORDER BY NEWID())
FROM @t
WHERE add1 IS NOT NULL
) AS adds
WHERE rn = 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources