Home » RDBMS Server » Server Administration » newbie who needs urgent help
newbie who needs urgent help [message #371112] Wed, 30 August 2000 06:39 Go to next message
Svehla
Messages: 1
Registered: August 2000
Junior Member
I have a problem... I have a question in a laboration that goes like this "List the customer which have bought the most copies of a specific title".. and I wrote it like this..

CREATE VIEW c4 AS
SELECT m.title, k.pnr, k.sname, k.fname, m.category, m.name, p.qty
FROM Faktura f, Konto k, Paket p, Media m
WHERE f.faktnr=p.paketnr AND k.pnr=f.pnr AND m.artnr=p.artnr
GROUP BY p.qty, k.pnr, m.name, m.category, m.title, k.sname, k.fnamn

select DISTINCT(title),MAX(qty), fname
from c4
where titel='Telling Stories'
GROUP BY qty, fname, title

// But the title is not DISTINCT... and I get a result that not at all just displays the maxiumum of "qty". To me it looks like sql only works row by row when I use several tables and I want to compare every row and only display one hit with the customer who has bought most copies

title fname
----------------------------------- ----------- --------------------
Telling Stories 6 hakan
Telling Stories 9 dummy1
Telling Stories 12 hakan
Telling Stories 12 martin

(4 row(s) affected)
Re: newbie who needs urgent help [message #371117 is a reply to message #371112] Thu, 31 August 2000 06:06 Go to previous message
Vjeko
Messages: 15
Registered: August 2000
Junior Member
Hi!

It isn't correct to group by the column which max is needed in select clause.

You should try this:

SELECT title,fname,qty
FROM c4
WHERE title='Telling Stories'
and qty=(select max(qty) from c4 where title='Telling Stories');

If you want to retrieve the customers with max copies for all titles then you should try this:

SELECT title,fname,qty
FROM c4
WHERE (title,qty) in (select title,max(qty) from c4 group by title);

Good luck!
Previous Topic: Problem with AIX permissions creating a datafile
Next Topic: Does unique constraints decrease performance ?
Goto Forum:
  


Current Time: Fri Oct 30 03:06:01 CDT 2020