Query Design: ORDER BY MIN(Name)

rbuser

Hi,

I am designing a data view with Calcs as MIN(ColName). I also want to
Sort by that MIN(ColName) field. But when I add MIN(ColName), my SQL becomes
ORDER BY 5. The complete SQL should be like:

SELECT a, b, c,d, MIN(ColName)
FROM tbl
GROUP BY a,b,c,d
ORDER BY MIN(ColName)

I make this work in SQL Server Query Analyzer. However, it doesn't work in
ReportBuilder. When I try to preview the data, it got an exception said
"Field 'ColName' not found".

Any suggestion?

Bin

nicocizik

Hi Bin,

Be sure the calculated field is not being assigned an alias. You may also
try, ORDER BY 5.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

1. How can I not assign an alias to the calculated field? It has a alias by
default. The alias is like MIN(ColName) and I cannot change it.

2. ORDER BY 5 does NOT work. That is how I am getting the error.

rbuser

Another way to obtain some good results is creating a View in your SQL
Server.

Could be like this:

Create view MinQuery as
Select a, b, c, d, Min(ColName) as MinColName
from tbl
Group by a, b, c, d
Order by Min(ColName)

This way your reference your dataview to the "MinQuery" object as a table
and you could also sort it as you want.

Regards,

Fabio Reynoso.

nicocizik

Hi Bin,

Sorry, I did not see that ORDER BY 5 was not working. What error are you
receiving? In my testing with SQL Server, the order by is working
correctly. In the calc tab of the Query Designer, you can assign your own
alias to each calculated field if you would like.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Dear Nico,
Before you answer this post, be sure read from the very beginning,
please!

1. I posted everything in the original post. The error I got is "Field
'ColName' not found."
2. I said in my second post: I can NOT change the alias 'In the calc tab of
the Query Designer'. It IS ReportBuilder's DEFAULT alias. And YOU told me in
your first reply 'Be sure the calculated field is not being assigned an
alias'.

3. Let me put it in short: How to ORDER BY a MIN(x) field? (In ReportBuilder
of course)

ps: If you don't know what the answer is, please sugguest a proper
newsgroup.

Thank you!

rbuser

Hi, Fabio!
Thank you for the tip!
I thought about this. The problem with this solution is I have to maintain
views in SQL Server. That means a view update in development needs update in
customers also. It is more work.
Any other sugguestion?

nicocizik

I apologize for the confusion. The reason I asked for the error message
again is that I am unable to recreate this error on my machine with SQL code
almost identical to your own. I wanted you to re-state it in case there was
something I missed.

I am testing with ReportBuilder 9.03, Delphi 7, and SQL Server 2000
(Northwind database). Below is the SQL code generated by ReportBuilder
(which works correctly). If you would please guide me as to how I can
recreate this error on my machine. If I remove the alias, the following
code works as well.

SELECT Products.ProductID, Products.ProductName, Products.UnitPrice,
MIN(Products.UnitPrice) MIN_Products_UnitPrice
FROM Products Products
GROUP BY Products.ProductID,
Products.ProductName,
Products.UnitPrice
ORDER BY 4

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I understand what you mean. I make a small project to repeat the error. The
ORDER BY 5 does show up but the error doesn't come out. Therefore, ORDER BY
5 could work. I'll check my own code.

Sorry for misunderstood you!
Bin

rbuser

> Before you answer this post, be sure read from the very beginning,

note that courtesy is welcome

Ed Dressel
Team DM