MSSQL vs mySQL when using DISTINCT 2

[1DMF]

Hello,

I'm having a problem getting my SQL code to work on MS SQL, when using the DISTINCT option.

I want to offer people the ability to sort the data by various fields which are not included in the selection or order by.

In mySQL this works, however MS SQL keeps erroring with...

Column "myColumn" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

But if I add it to the group by clause it then errors with..

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

How do I get MS SQL to work the same way mySQL does when trying to order using columns not in the selection?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[bborissov]

Post the query.
It is hard to me to say what is wrong in it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[1DMF]

here is the code (perl)...

my @rs = &getSQL("$main::dbtable","DISTINCT $rep, COUNT(1) AS cnt","($where) GROUP BY $rep","$order");

What it equates to for example could be..

 SELECT DISTINCT version, COUNT(1) AS cnt FROM MyTable WHERE 1=1 GROUP BY version ORDER BY phrase

The field being selected is dynamic as is the where clause and the order by which are all independent depending on what is selected via the admin interface tool.

What I don't want to do is add the 'order by' into the select otherwise you will no longer get the correct distinct records for the graph which is produced.

It all works fine in mySQL, it's just MS SQL throwing a paddy! so your help finding a solution is much appreciated.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

I can't believe that works in MySQL. I think I just lost a little more respect for that product.

The distinct isn't (shouldn't be) necessary because you are only returning one non-aggregated column (version), which you are grouping by, so the results must be distinct anyway.

Each version could have any combination of phrase, or no phrase at all. Ordering on this column (from a data perspective) does not make sense.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

cool, so because only one column name is ever used only group by is required, d'oh, why didn't I think of that.

It's a habbit to use DISTINCT when I want to ensure unique records are returned, best I get out of it - lol

Ordering on this column (from a data perspective) does not make sense.

it does if you want to change the order in which the graph is plotted separate from the data being shown.

At least is looks like I can have a single SQL statement regardless of DB connection, many thanks.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

It's a habbit to use DISTINCT when I want to ensure unique records are returned

In performance terms, DISTINCT is relatively expensive. If you are using a GROUP BY, there should be no need to DISTINCT.

If you think your data should be DISTINCT to begin with, and it's not, chances are you are missing a join condition or you have bad data in a table somewhere.

With a properly designed database and some well written queries, there's very few occasions where a query requires DISTINCT.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

writing ORDER BY for a column that is not in the intermediate result set does not make sense

the intermediate result set is what is produced by the GROUP BY operation -- there is one intermediate row produced for every unique combination of GROUP BY columns

so obviously, if a column is not in this intermediate result set, then you can't sort by it

example:

SELECT schoolname, COUNT(*) AS number_of_students
FROM students
GROUP BY schoolname

it just wouldn't make sense to add ORDER BY teacher because there are multiple teachers per school

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[1DMF]

So basically you can only sort by columns not in the select if using mySQL.

I've just tried it in SQL and it doesn't work..

SELECT version, COUNT(1) AS cnt FROM myTable WHERE (position <= 100) GROUP BY version ORDER BY id ASC

causes

Column "myTable.id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

I think i'm going to have to remove the ability to give sort order when using on an MS platfrom, unless someone can think how I can group by the selected column but order by another column in the DB table.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

What you could do is include another aggregate and order by that.

Rudy provide a great example.

SELECT schoolname, COUNT(*) AS number_of_students
FROM students
GROUP BY schoolname

it just wouldn't make sense to add ORDER BY teacher because there are multiple teachers per school

What you could do is use an aggregate for the column you want to order on. For example:

SELECT schoolname, COUNT(*) AS number_of_students
FROM students
GROUP BY schoolname
Order By [!]Min([/!]TeacherName[!])[/!]

I don't know if this is the appropriate solution for your problem. It is something to consider.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

george, although your solution will actually execute (and does fix the syntax problem), it still doesn't make a lot of sense to list schools in the order of the lowest teacher's name

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

Rudy, I completely agree with you!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

George, it's working perfectly on MS SQL.

I really appreciate your help, many thanks.

george, although your solution will actually execute (and does fix the syntax problem), it still doesn't make a lot of sense to list schools in the order of the lowest teacher's name

that's because you are not thinking outside the box


This is a graph, not tabular record set data, the users now has the ability to make the graph columns appear in what ever order they wish based on exisiting table columns regardless of the report they are running and columns selected for the reporing data.

Why stop a user having a function just because you beleive it makes no sense, it makes perfect sense when you look at the user GUI and the resulting graphs ;-)





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[r937]

it makes perfect sense when you look at the user GUI and the resulting graphs

oh, yeah, fer shure, i'm lookin at them right now, using Microsoft® CrystalBall© )


but it ~still~ doesn't make sense if you have a column in the ORDER BY that isn't in the SELECT, or isn't an aggregate function like MIN(teachername)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[1DMF]

lol - if you really want to see it in action

http://dance_music.org/gre_admin

You'll see the GUI ( ok crappy form ;-) ) has a sort filter, I didn't want to remove this functionality just because you switch to 'display as graph'.

Plus I coded it and first tested on mySQL, and it worked, I didn't realise I would run in to these problems with MS SQL.

hey ho it's all workin on both platforms now, so thanks for the help guys

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL Beta v1.3 ( with graphs )