Exercise 16 - 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

General SQL Server Forums

New to SQL Server Programming

Exercise 16

Author

Topic

yoyosh
Starting Member

27 Posts

Posted - 2012-04-11 : 06:49:37

I'm trying to do exercise 16 from sql-ex.ru which says:

Find the pairs of PC models having similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i).
Result set: model with higher number, model with lower number, speed, and RAM.

I tried to follow hints from their page but my solution fails on second checking database. I have no idea what may be wrong. Could you please give me any hints?

Here is my proposed solution:


select pc1.model, pc2.model, pc1.speed, pc1.ram
from pc pc1
inner join pc pc2 on (pc1.speed = pc2.speed and pc1.ram = pc2.ram and pc1.model <> pc2.model)
where pc1.model > pc2.model

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-11 : 11:25:16

What do you mean it "fails?" Is it not returning the data you expect or is it generating an error?

The query looks correct to me, but if it is not returning the results you expect, please post DDL, DML and expected output. Here are a couple of links tht can help you provide that in a consumable format:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

yoyosh
Starting Member

27 Posts

Posted - 2012-04-12 : 01:12:19

Unfortunatelly it fails on second checking database. I don't have direct access to data in this database so it is hard to figure out what may be wrong. Most of the times in means that my query seems to be rather alright but in some specific situations it produces wrong results.

All I know is that on the second DB it returns the following error:
* Wrong number of records (more by 2)

There is also hint saying:

You should eliminate duplicates, since the same model (with identical characteristics of speed and RAM) can be presented more than one times in PC table.

yoyosh
Starting Member

27 Posts

Posted - 2012-04-12 : 03:32:50

I used that hint and selected only distinct values from this table. That worked. At last

mr_hitman
Starting Member

1 Post

Posted - 2013-02-17 : 14:35:36

WELL im NEW to SQL and IM on Q 16 This IS what I WROTE & its BASICALLY the SAME i THINK. but IT doesnt GIVE me THE error FROM ur QUERRY

SELECT pc1.model, pc2.model, pc1.speed, pc1.ram
FROM pc pc1

INNER JOIN pc pc2 ON
(pc1.speed = pc2.speed AND pc1.ram = pc2.ram AND pc1.model <> pc2.model)
GROUP BY pc1.model, pc2.model, pc1.speed, pc1.ram
HAVING pc1.model > pc2.model

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources