SELECT		writer.code_w,
		book.title,
		COUNT(buy.code_book)
FROM		writer
INNER JOIN	buy ON buy.code_book = writer.code_book
INNER JOIN	book ON book.code_book = buy.code_book
WHERE		book.price = (SELECT MAX(book.price) FROM book AS b WHERE b.code_book = buy.code_book)
GROUP BY	writer.code_w,
		book.title

quote:

---

Originally posted by Peso

Homework?

SELECT		writer.code_w,
		book.title,
		COUNT(buy.code_book)
FROM		writer
INNER JOIN	buy ON buy.code_book = writer.code_book
INNER JOIN	book ON book.code_book = buy.code_book
WHERE		book.price = (SELECT MAX(book.price) FROM book AS b WHERE b.code_book = buy.code_book)
GROUP BY	writer.code_w,
		book.title

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

Homework?

SELECT		writer.code_w,
		book.title,
		COUNT(buy.code_book)
FROM		writer
INNER JOIN	buy ON buy.code_book = writer.code_book
INNER JOIN	book ON book.code_book = buy.code_book
WHERE		book.price = (SELECT MAX(book.price) FROM book AS b WHERE b.code_book = buy.code_book)
GROUP BY	writer.code_w,
		book.title

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

Do you have more than two writers in buy table?

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

You wrote you get two records back, that mean you have at least two records in Buy table.
And your original question was about counting (for each writer the most expensive book) how many were sold.

I think you need to read this
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

Where is sample data for table Buy?

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Good morning. Have a tiny problem. Have 3 tables
writer
code_w,code_book

buy
code_client,code_book

book
code_book,title,price

---

quote:

---

Originally posted by Peso

That is not sample data. That is table definition.

You posted sample data for writer and book tables like this:
table writer
code_w,code_book
w01,b01
w01,b02

table book
code_book,title,price
b01,history,300
b02,maths,100

where is buy table sample data?
table buy
xx1, yy1, zz1
xx2, yy2, zz2

---

E 12°55'05.63"
N 56°04'39.26"

---

-- Prepare sample data
SET NOCOUNT ON

DECLARE	@Writer TABLE
	(
		code_w CHAR(3) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Writer
SELECT	'w01', 'b01' UNION ALL
SELECT	'w01', 'b02'

DECLARE	@Book TABLE
	(
		code_book CHAR(3),
		title VARCHAR(7),
		price INT
	)

INSERT	@Book
SELECT	'b01', 'history', 300 UNION ALL
SELECT	'b02', 'maths', 100

DECLARE	@Buy TABLE
	(
		code_client CHAR(4) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Buy
SELECT	'cl02', 'b01' UNION ALL
SELECT	'cl03', 'b01' UNION ALL
SELECT	'cl04', 'b02'

-- Display initial values
SELECT * FROM @Writer
SELECT * FROM @Buy
SELECT * FROM @Book

quote:

---

Originally posted by Peso

Now we finally are getting forward!
What is your expected output based on this sample data below?

-- Prepare sample data
SET NOCOUNT ON

DECLARE	@Writer TABLE
	(
		code_w CHAR(3) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Writer
SELECT	'w01', 'b01' UNION ALL
SELECT	'w01', 'b02'

DECLARE	@Book TABLE
	(
		code_book CHAR(3),
		title VARCHAR(7),
		price INT
	)

INSERT	@Book
SELECT	'b01', 'history', 300 UNION ALL
SELECT	'b02', 'maths', 100

DECLARE	@Buy TABLE
	(
		code_client CHAR(4) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Buy
SELECT	'cl02', 'b01' UNION ALL
SELECT	'cl03', 'b01' UNION ALL
SELECT	'cl04', 'b02'

-- Display initial values
SELECT * FROM @Writer
SELECT * FROM @Buy
SELECT * FROM @Book

Note that I followed the advice given in the link above about how to post questions for forums...

---

E 12°55'05.63"
N 56°04'39.26"

---

-- Prepare sample data
SET NOCOUNT ON

DECLARE	@Writer TABLE
	(
		code_w CHAR(3) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Writer
SELECT	'w01', 'b01' UNION ALL
SELECT	'w01', 'b02'

DECLARE	@Book TABLE
	(
		code_book CHAR(3),
		title VARCHAR(7),
		price INT
	)

INSERT	@Book
SELECT	'b01', 'history', 300 UNION ALL
SELECT	'b02', 'maths', 100

DECLARE	@Buy TABLE
	(
		code_client CHAR(4) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Buy
SELECT	'cl02', 'b01' UNION ALL
SELECT	'cl02', 'b01' UNION ALL
SELECT	'cl03', 'b01' UNION ALL
SELECT	'cl04', 'b02'

-- Display the result
SELECT		TOP 1 WITH TIES
		w.code_w,
		b.Title,
		b.Price,
		y.Books,
		y.Clients
FROM		@Writer AS w
INNER JOIN	@Book AS b ON b.code_book = w.code_book
INNER JOIN	(
			SELECT		code_book,
					COUNT(*) AS Books,
					COUNT(DISTINCT code_client) AS Clients
			FROM		@Buy
			GROUP BY	code_book
		) AS y ON y.code_book = b.code_book
ORDER BY	ROW_NUMBER() OVER (PARTITION BY w.code_w ORDER BY b.Price DESC)

quote:

---

Originally posted by misty

quote:

---

Originally posted by Peso

Now we finally are getting forward!
What is your expected output based on this sample data below?

-- Prepare sample data
SET NOCOUNT ON

DECLARE	@Writer TABLE
	(
		code_w CHAR(3) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Writer
SELECT	'w01', 'b01' UNION ALL
SELECT	'w01', 'b02'

DECLARE	@Book TABLE
	(
		code_book CHAR(3),
		title VARCHAR(7),
		price INT
	)

INSERT	@Book
SELECT	'b01', 'history', 300 UNION ALL
SELECT	'b02', 'maths', 100

DECLARE	@Buy TABLE
	(
		code_client CHAR(4) NOT NULL,
		code_book CHAR(3) NOT NULL
	)

INSERT	@Buy
SELECT	'cl02', 'b01' UNION ALL
SELECT	'cl03', 'b01' UNION ALL
SELECT	'cl04', 'b02'

-- Display initial values
SELECT * FROM @Writer
SELECT * FROM @Buy
SELECT * FROM @Book

Note that I followed the advice given in the link above about how to post questions for forums...

---

E 12°55'05.63"
N 56°04'39.26"

---

dont know i should put all this. sorry. i expect
w01,history,2

Microsoft SQL Server 2005 and later

---

quote:

---

Originally posted by visakh16

did you move from Oracle?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119415

---