Hello

I want to be able to sort via a count returned from a subquery.

SELECT tblProduct.sDescription FROM tblProduct WITH (NOLOCK) ORDER BY ( SELECT Count(*) FROM tblStock WITH (NOLOCK) WHERE tblStock.ifkProductID = tblProduct.ipkProductID )

What generated code would I use so I can put it in the Sort to sort by a sub query?

Hi Murrell,

I would do this in 1 of 2 other ways:

1.) Actually Join of tblProduct & tblStock and GROUP BY tblProduct.ipkProductID. Then add a SELECT field for Count(*) AS SortOrder. and SORT BY SortOrder.

or

2.) Put your Subselect in the SELECT clause. ( Subselect stuff ) AS SortOrder. and SORT BY SortOrder

Hope this helps!

Ryan

Hey Ryan

I don't want to do an INNER JOIN as there are a lot of columns. I have showed a simplified version of it. That is why I thought of a sub query. I know it ain't the best option in terms of query optimisation but it will suffice.

Regards, Simon

Hi Simon,

Not sure if a subselect within the order clause is possible with LLBLGen. Of course, I could be wrong. However, there are other ways to do what you're trying to do as listed above.

Why would an INNER JOIN be impacted by having numerous columns, If you can use the SELECT clause to determine what columns are returned?

Ryan

rdhatch wrote:

2.) Put your Subselect in the SELECT clause. ( Subselect stuff ) AS SortOrder. and SORT BY SortOrder

A sub-select in the select lise is the best option, indeed. Use a Scalar query expression for this.

Walaa, do you have a sample code snippet of how it should be done properly?

Walaa wrote:

rdhatch wrote:

2.) Put your Subselect in the SELECT clause. ( Subselect stuff ) AS SortOrder. and SORT BY SortOrder

A sub-select in the select lise is the best option, indeed. Use a Scalar query expression for this.

To use the Scalar Query Expression, please check the example found in the docs.

Then it would be easy to use the field were the expression was applied in a sortExpression.

e.g. SortExpression sorter = new SortExpression(fields[1] | SortOperator.Descending);

And pass this to the fetch method.

Perfect, thanks. I will give it a try!!

Walaa wrote:

To use the Scalar Query Expression, please check the example found in the docs.

Then it would be easy to use the field were the expression was applied in a sortExpression.

e.g. SortExpression sorter = new SortExpression(fields[1] | SortOperator.Descending);

And pass this to the fetch method.