Qualified minimum.

[denimined]

I have an inherited an incredibly ugly SQL statement

INSERT INTO tmptbl (KEY, ERROR)
SELECT KEY, MIN(ERROR) AS ERROR
FROM (<incredibly ugly statement>) AS Q
GROUP BY KEY

that returns the lowest error state. Of course, there is now a new error check that needs to return, prior to other checks. Rather than renumbering all the codes (shudder), I was thinking - what about using a priority for the error codes, then select...

So - If the interior part of the ugly SQL statement returns something like:
(Key, error code, priority)

ABC, 41, 3
ABC, 72, 2
ABC, 99, 2
DEF, 66, 3

What I would want returned as a result would be:

ABC, 72
DEF, 66

(Lowest code for lowest priority of each key.)

How do I get there?
Thx.

 

[yelworcm]

with priorities ([key], [error code], priority, rownum)
as
(select [key], [error code], priority, row_num() over (partition by [key] order by priority)
from table)

select *
from priorities
where rownum = 1

Note, I usually forget the order of partition and order, so you may need to tinker with this a touch.

 

[denimined]

Well... Thanks and DRAT...

Built it all up, tested it in Studio - everything was good. Put it into VB6 code and... Fall down, go boom. Turns out ADO doesn't know about WITH (or ROW_NUMBER or PARTITION BY). Any suggestions on doing this a different way? Since the content of the ugly SQL statement is built up by configuration switches and what's actually calling the function, I can't put it into a stored procedure, either.

 

[djj55]

Can you create a stored procedure to call instead?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[wbodger]

If it is built and configured could you create a function? That way you could send it variables.

wb

 

[gmmastros]

Can you show the connection string you use in VB6?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

For what it's worth, I do a lot of programming with VB6 using ADO and SQL. I just confirmed that I can run a query that has with, Row_Number() and Partition By. No problems whatsoever. This is why I was asking about your connection string.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[denimined]

Got there the old school way.
Given that Error is always < 100, went with:

SELECT Key, 
       Error - (FLOOR(Error / 100) * 100) AS Error
FROM (
      SELECT Key, 
             MIN(Error + (PriorityLevel * 100)) AS Error
      FROM (<Ugly SQL code>) AS TBL
      GROUP BY Key
     ) AS TBL

Sometimes elegance simply has to give way to the hammer...

Thank you all.

 

[denimined]

George -
Here is the connection in VB.
It is just the user selected UDL file name.

Public cnnp As New ADODB.Connection 'Global database connection object

cnnp.ConnectionString = "File Name=" & cpRootPath & cmDataSource & ".UDL"

The reference object is system\ado\msado25.tlb

Is that what you were asking for, or am I missing something?