Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Exercise 16
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

27 Posts

Posted - 04/11/2012 :  06:49:37  Show Profile  Reply with Quote
I'm trying to do exercise 16 from 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

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/11/2012 :  11:25:16  Show Profile  Reply with Quote
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:
Go to Top of Page

Starting Member

27 Posts

Posted - 04/12/2012 :  01:12:19  Show Profile  Reply with Quote
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.
Go to Top of Page

Starting Member

27 Posts

Posted - 04/12/2012 :  03:32:50  Show Profile  Reply with Quote
I used that hint and selected only distinct values from this table. That worked. At last
Go to Top of Page

Starting Member

1 Posts

Posted - 02/17/2013 :  14:35:36  Show Profile  Reply with Quote
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

(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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000