I don't remember him writing so, but I think I will believe you.

It's your report, not mine.

Yes, but unlike Access, the view must contain one table only and have an identity column.

antonio.collins (2/5/2008)

---

{YourTableNameHere} is not a table, it is the result of a query that runs in 150s. Your technique is post-processing that entire result set after it has completed....

antonio.collins (2/5/2008)

---

the query that produces our 22K row ranked result set takes 150s. i was looking for some top N wizardry to cajole the query engine to skip producing...

antonio.collins (2/5/2008)

---

Peso, your technique is still dealing with a 22K row result set that took 150s to produce. Any technique that focuses on getting data out of that result...

This?

DECLARE @Sample TABLE (Gadget CHAR(8), Price MONEY)

INSERT@Sample

SELECT'Widget A', 74.85 UNION ALL

SELECT'Widget A', -74.85 UNION ALL

SELECT'Widget A', 14.97 UNION ALL

SELECT'Widget A', 4.99 UNION ALL

SELECT'Widget A', 49.90...

Dates need to be enclosed with single quotes.

DECLARE @the_date_string NVARCHAR(30),

@the_date DATETIME,

@sql_string NVARCHAR(100)

SELECT@the_date_string = '01/31/2008 9:33 PM',

@the_date = CONVERT(datetime, @the_date_string, 101)

SET@sql_string = 'SELECT ''' + @the_date_string + ''' AS theDate'

EXEC(@sql_string)

Something similar to

SELECT TOP 10Col1,

SUM(Col2)

FROMTable1

GROUP BYCol1

HAVINGSUM(Col2) > 0

ORDER BYSUM(Col2) DESC

My suggested algorithm fetches only the top 10 records based on the column that ranking is calculated on.

I then calculate the ranking for only these 10 records.

Then I eventually fetch...

Try to add a UNIQUE index or an UNIQUE constraint.

I have no idea what you mean.

Do you have 5 tables named "1", "2", "3", "4" and "5"?

Do you have 1 table with 5 columns named "1", "2", "3", "4"...

Well, in my temp table algorithm above it takes an average of 80 milliseconds to fill the temptable and get the rankings calculated...

Are you sure we are talking about the...

SELECTPoleID,

FROMTable1

GROUP BYPoleID

HAVINGMAX(CASE WHEN AttachDate IS NOT NULL AND PoleDT IS NULL THEN 0 ELSE 1 END) = 0

antonio.collins (2/5/2008)

---

Hey guys, thanks for all your suggestions. I was hoping we'd find some syntax or technique that could produce a rank() and allow the query engine to skip/ignore...

I don't think COALESCE will work in this case.

Try

SELECTA.EMPLID AS PERSON_NBR,

'005/116/' + LEFT(A.DEPTID, 5) + '/' + A.JOBCODE + '/NA/NA/NA' AS LABORACCOUNT,

0 AS HOURLY_RT,

'' AS SUPERVISOR_NAME,

A.EMPL_STATUS AS EMPL_STATUS,

D.SERVICE_DT AS HIRE_DT,

''...