| newbie who needs urgent help [message #371112] |
Wed, 30 August 2000 06:39  |
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  |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
