Select Distinct Records (MSSQL)

QuestionsSelect Distinct Records (MSSQL)
Mike Sheppard asked 8 years ago

I am using MS SQLServer and I cannot do a select distinct(field) SQL Command – I get a server error.

Some issues occured in this operation, Contact technical support for help

I am using odbc_mssql

Any help is greatly appreciated (a quick overview of the pertinent code is below).

If I remove the "distinct" operator the code works fine – however the data-set I am returning will have duplicate rows that need to be parsed.

Please advise

Thanks

Mike

—————-
$db_conf = array();
$db_conf["type"] = "odbc_mssql";
$db_conf["server"] = "Driver={SQL Server};Server=XXXX;Database=XXXX;";

—————-
$g->select_command = "SELECT DISTINCT(FIELD) FROM TABLE";

7 Answers
Abu Ghufran answered 8 years ago

Try giving an alias for this selection.

$g->select_command = "SELECT DISTINCT(FIELD) as f FROM TABLE";

Mike Sheppard answered 8 years ago

That's what I normally do – but it was failing, so I simplified it as much as possible. Same error.

I have used this method successfully with odbc_mssql_native – but I didn't see that option in the drivers folders for this package.

Thanks for the quick response.

Mike

Abu Ghufran answered 8 years ago

Try setting:

$g = new jqgrid($db);

$g->con->debug = 1; // changed from 0 to 1
$g->debug = 1;

This will show exact reason for why query is failing.

Mike Sheppard answered 8 years ago

I have tried numerous combinations of "type" and "server" (see examples below) all with the same results – as soon as I put-in the Distinct Operator, the query fails.

Thanks again for any help you can offer.

$db_conf["type"] = "odbc";
$db_conf["type"] = "odbc_mssql";
$db_conf["type"] = "odbc_mssql_n";

$db_conf["server"] = "Driver={SQL Server}
$db_conf["server"] = "Driver={SQL Server Native Client 10.0}
$db_conf["server"] = "Driver={SQL Server Native Client 11.0}

Mike Sheppard answered 8 years ago

I didn't think about turning-on error logging (I even had it on earlier to solve another issue).

The problem seems to be with the way the records are being returned with the select top (1+0) at the beginning . . . that makes the command invalid.

(odbc_mssql): select top (1+0) DISTINCT(field) as id from TABLE WHERE 1=1

Thanks for the continued support.

Mike Sheppard

Abu Ghufran answered 8 years ago

Try using the main query as subquery:

$g->select_command = "SELECT * FROM ( SELECT DISTINCT(FIELD) as f FROM TABLE ) as s";

Ref: http://stackoverflow.com/a/20279145/385377

Mike Sheppard answered 8 years ago

That was a good work-around – it appears to be working for my needs – I still need a bit of testing on the more complex query – but it does appear to have solved the issue. Thanks for the great tip and the very rapid and accurate support.

Mike

Your Answer

14 + 17 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?