elmerbotha (6/23/2008)

---

When I get the resultset a second, or nth time, I obviously need to take into account the 'thrown away values' so they are not returned again, but rather...

Hello

Here's your data prepared as a...

elmerbotha, can you please provide a larger sample size, say 5 or 10 rows for two metals? Presumably in your actual data you will have several rows like this:

Metal ...

Yep Rich it's more efficient. The correlated subquery is evaluated once for every row in the publishers table: it's slow and doesn't scale well. If you have a large enough...

Hello

Something like this is quite efficient:

SELECT p.Publisher, ISNULL(b.BookCount, 0) AS BookCount

FROM Publisher p

LEFT JOIN (

SELECT Publisher, COUNT(*) AS BookCount

FROM Books

GROUP BY Publisher) b

ON b.Publisher = p.Publisher

ORDER...

smunson (6/11/2008)

---

You're quite right! And that's why you're an "Old Hand", and I'm still an "SSC Rookie". I was able to make the same change to...

smunson (6/11/2008)

---

Actually, I tried that code and it fails on syntax, due to the ORDER BY clause being present in a subquery. Try the following that uses table...

This works too:

CREATE TABLE #Employees (Emp_No INT, EName VARCHAR(5), Sal INT)

INSERT INTO #Employees (Emp_No, EName, Sal)

SELECT 111, 'AAAA', 9999 UNION ALL

SELECT 222, 'bbbb', 9890 UNION ALL

SELECT 777, 'vvvvv',7879

SELECT TOP 1...

Same topic posted three times. Only 885 more to go.

No kidding 😉

Not at the same time - or to each other!!

Adam Watson (5/6/2008)

---

Thanks for that suggested alternative, worked a treat - I'll rewrite the whole proc in that format as I totally agree that it is a lot more readable....

Hi Adam

Currently you're doing 20 selects into the same table to get your values. You could consolidate this into a single select and make the first part of your code...

Heh heh no worries Grant, you are of course absolutely right. In practice though, about 7 years of it, the table layout I've shown above was far easier to work...

The two fashion systems I've written in the past worked like this:

Column Name

StyleID

ColourID

Size06

Size08

Size10

Size12

.

.

.

Size 24

Unsized

The meaning of the sizing columns is a property of the Style master, so Size06 to Size18...