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.
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.ramfrom pc pc1inner 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 QUERRYSELECT pc1.model, pc2.model, pc1.speed, pc1.ramFROM pc pc1INNER 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.ramHAVING pc1.model > pc2.model |
|
|
|
|
|
|
|