SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

yoyosh
Starting Member

27 Posts
Posted - 04/11/2012 :  06:49:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3821 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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

yoyosh
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

yoyosh
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

mr_hitman
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

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000