a complex and challenging problem...

edal11

Hi,

I'm trying do make a script that puts all columns that need to be in the group by clause automatically.

This is because, I have a rather large program that works with MySQL. Now I want to make it work with MS SQL. The problem is that the SQL Statements aren't compatible.

So I'm making a script that will take a MySQL compatible SQL Statement and convert it to a MSSQL compatible SQL Statement.
But now I'm stuck, MSSQL doesn't like queries like:

SELECT field1, field2, field3, sum( field4 )
FROM mytable
GROUP BY field1

It will give an error because field1, field2 and field3 are not in the group by clause. This works with MySQL!

So, how can I take the previous query, and in some way append to the group by clause the fields that need to be in the group by????

Check this other example:

SELECT *
FROM mytable
GROUP BY field1

It seems rather complicated to me.... maybe some of you can think of a solution. Quite a challenge isn't it?? 🙂

Thanks!

barand

SELECT field1, field2, field3, sum( field4 )
FROM mytable
GROUP BY field1

It will give an error because field1, field2 and field3 are not in the group by clause. This works with MySQL!

I can see your problem, MySQL is really sloppy in this respect. If you are not grouping by or aggregating fields 2 and 3

a) why would you select them, and
b) why does it let you?

edal11

a) why would you select them, and

I see your point, I shouldn't...... my mistake....

why does it let you?

Well, MySQL does something like this:

| field1 | field2 | field3 | field4 |

| 1 | 5 | abc | 2 |
| 2 | 6 | xyz | 1 |
| 3 | 5 | asd | 2 |

| 4 | 6 | qwe | 3 |

Let's say we have this table, and we execute the following SQL Statement:

SELECT field1, field2, field3, sum( field4)
FROM T1
GROUP BY field2

We should get 2 records as a result of grouping by field2. Field4 won't be a problem because it's a SUM.
Then, what MySQL does is it will take the value of field3 and field1 from the first record of the grouped by records.

We would get this as a result:

| field1 | field2 | field3 | field4 |

| 1 | 5 | abc | 4 |

| 2 | 6 | xyz | 4 |

Can you see what I'm trying to explain? 🙂

The problem is that I can't just take the same SQL Statement and use it directly with MSSQL, because MSSQL is more strict and won't allow this. 🙁
Anyway, MySQL shouldn't allow it either.

Sxooter

Can you just enclose the ungrouped by fields with max() or min()???

select max(field1), field2, max(field3), sum(field4) from table group by field2;

???