Multiple Selects - 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 2000 Forums

SQL Server Development (2000)

Multiple Selects

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-07 : 05:57:43

Hello.
Im writing this query that get distinct values


SELECT  DISTINCT   dbo.MULTICONTRACT.ID,  dbo.DEFCAR.ID,  dbo.DEFCAR.DEFDESCRIPTION

FROM         dbo.MULTICONTRACT INNER JOIN
                      dbo.DEFCAR ON dbo.MULTICONTRACT.ID = dbo.DEFCAR.MULTICONTRACTID INNER JOIN
                      dbo.CARMODEL ON dbo.DEFCAR.CARMODELID = dbo.CARMODEL.ID
where (dbo.CARMODEL.DESCRIPTION like 'M%') --- and (DEFVALUE > 5000.000000)
ORDER BY dbo.DEFCAR.DEFDESCRIPTION DESC

Now i want from this values to select the 10 first
(select top 10*
from dbo.multicontract.id)
who can i do this?
Thanks.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 06:03:03

"Im writing this query that get distinct values "

Could you drop the DISTINCT if you used an EXISTS on [CARMODEL] instead of a JOIN? because if you can that will be more efficient.

Do you mean you then want to combine "distinct" and "top 10" ? because if you do that's fine, and will work, if not please describe the problem further

Kristen

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-07 : 06:12:44

New to sql so if i say something stupit...
"Could you drop the DISTINCT if you used an EXISTS on [CARMODEL] instead of a JOIN? because if you can that will be more efficient."
How can i do that? i have other contitions in where (dbo.CARMODEL.DESCRIPTION like 'M%')

"Do you mean you then want to combine "distinct" and "top 10""
Yes, when i get my rows i then want to do a top 10.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 06:43:39

[code]
SELECT
-- DISTINCT -- Shouldn't need this any more
TOP 10
dbo.MULTICONTRACT.ID, dbo.DEFCAR.ID, dbo.DEFCAR.DEFDESCRIPTION
FROM dbo.MULTICONTRACT INNER JOIN
dbo.DEFCAR ON dbo.MULTICONTRACT.ID = dbo.DEFCAR.MULTICONTRACTID
where EXISTS
(
SELECT *
FROM dbo.CARMODEL
WHERE dbo.DEFCAR.CARMODELID = dbo.CARMODEL.ID
AND dbo.CARMODEL.DESCRIPTION like 'M%'
)
ORDER BY dbo.DEFCAR.DEFDESCRIPTION DESC
[/code]
Kristen

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-07 : 06:57:48

Thanks.
Any particular reason that you didn't include
INNER JOIN
dbo.CARMODEL ON dbo.DEFCAR.CARMODELID = dbo.CARMODEL.ID

if i exlude it on the above (my) query i get record losses

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-07 : 07:02:01

Oh.
Sorry.
You have it down on the where, ok.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 07:16:51

This is to stop duplicates being selected, and thus you don't need DISTINCT (which requires SQL Server to make a Sort in order to remove the duplicates, which takes longer).

The EXISTS only has to find one matching record to discover that the rest of the data is "OK", whereas the JOIN includes ALL matching records, which may be millions!!, which then need to be removed by the DISTINCT ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources