MySQL to SQL Server migration

Hi,
I would liked to know the SQL Server equivalent for the below MySQL query :
SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1
Thanks,
SmithaYou can do it the same way except you can't just show a.col2. You use groupby, so it must belong to some logical output. You can do it like:

SELECT
a.col1,
max(a.col2), --or other functions...
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1

OR

SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2

Hope it helps|||Adding all the select columns to the group by can't be the solution as the result will differ when compared to the MySQL query. Am I right ? Please advice.
Also I can't remove the select list item and replace it with aggregate function, as I require that too in my result set.|||imho, MSSQL is here logically correct. Which raw should sql give out if you use group by clause?

id | name
1 a
2 b
2 c
2 d
3 e
If you execute SELECT name, max(id) GROUP BY id, what should come out?
1 a
3 e
2 (?) <-- b, c or d?

So you have to be specific.|||MySQL always gives the first row '2 b'. I need the equivalent for this logic.|||Yes, and that's not definitely. MySQL gives out just the first row, and that's the first row saved in the table. The next time (say, after import-export) it may be an another row.|||SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2
Your above solution gives all the rows, is there any equivalent SQL Server query which fetches the first row from the table
|||SELECT DISTINCT ?|||SELECT distinct MAX(id), name FROM test3 GROUP BY id, name
Above query is not helping me ! It gives all the rows|||SELECT MAX(id), min(name) FROM test3 GROUP BY id

here you'll get the right grouping. If you take min(name) you'll get the first (alphabetically) grouped name row|||Can you show some sample data and the output for that query in MySQL? I'm not sure how it should behave, given that Col1 is both grouped and in an aggregate function, and Col2 is not even grouped... Very odd... If you can show the input you're using and the output you need, I can help you write a query for it. Also, please note whether you're using SQL Server 2000 or 2005. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <smith_kbase@.discussions.microsoft.com> wrote in message news:edc1b9b6-7367-4041-8e24-a17af5660785_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi,I would liked to know the SQL Server equivalent for the below MySQL query :SELECTa.col1,a.col2,max(a.col1)FROM test a,test1 b WHERE a.col2 = b.col2GROUP BY a.col1Thanks,Smitha

MySQL to SQL Server - query migration

Hi,
I would liked to know the SQL Server equivalent for the below MySQL query :
SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1
Thanks,
MiraJSELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1,a.col2

MySQL Style INSERT INTO SET

MySQL allowed INSERT statements that looked like this:

INSERT INTO Table SET col1 = value1, col2 = value2, col3 = value3

Basically this closely matched the format of the UPDATE statements.

Here is why this was good: I could do something like this:

if (RecordExists ==false) { SQLquery ="INSERT INTO Table SET ";}else { SQLquery ="UPDATE Table SET ";}SQLquery +="col1 = value1, ";SQLquery +="col2 = value2, ";SQLquery +="col3 = value3, ";SQLquery +="col4 = value4 ";if (RecordExists ==true) { SQLquery +="WHERE id = " + ourID;}

If I had a good 50 columns then it means I can reuse a sizable chunk of code for both INSERT and UPDATE statements (since both use the same general format). Whereas if I have to use "INSERT INTO Table(columns) VALUES (values)" then I'm look at duplicating a lot of code.

Does MS-SQL support something similar to "INSERT INTO SET"? How are others dealing with this?

Thanks,

Gabe
-------

No. That is a proprietary MySQL syntax.

How do I deal with it? I don't. I don't use SQL concatenation techniques. They make the application/web apps insecure, difficult to port, slow, hard to maintain, and locks the application developer into doing database design (Which isn't good for team development). Not only that but it limits what you can do from the database design, and in multi-application environments where you have many applications using the same data tables, difficult to coordinate since the data-logic has now been moved to each application.

And lastly, for some applications it's simply isn't even an option. For example in many HIPAA applications, since you are allowing applications to retrieve data without the ability to forcably log the request, it's not even an option to do it that way.

|||

Motley, you answered my question and for that I thank you.

The code I was showing was simplified for forum purposes. (I just wanted to demonstrate the overall logic.) In a real situation I would make use of the AddParameter method. But this probably still qualifies as using SQL concactenation and in-line SQL, which I know many consider a sin.

In my experience application developers are almost always are up to their eye-balls in database design anyway...even when working with a DBA. I know I always am. Many database search queries I deal with are tied closely to application conditions and have a lot of processing logic involved in them. I could do that processing logic in C# (which is extremely feature rich, has great development tools available & benefits from access to my application objects) or I could do it in a SQL stored procedure which seems generally clunky & not nearly as robust. Using lots of stored procedures normally just means I have one more place I have to update something or one more person I have to involve for a fix.

I'm not going to pretend to be an expert on this and some of this just comes down to getting the job done. Many of the security advantages you speak about could probably be mimiced by simply having well defined user roles. From what I've read the performance advantages aren't as overwhelming as people seem to believe. I have read that many of the performance enhancements MS-SQL makes to queries are extended to in-line queries as well.

From an application standpoint having a well developed set of database interaction objects becomes a dream. I know a ton of people are going to disagree with me and I may even change my mind on this subject someday. For me personally, most of the stuff you speak of has just proven to be a giant PITA.

Gabe
============

|||What is a PITA?|||And why SQL concatenation techniques make the application/web apps insecure??|||

And why SQL concatenation techniques make the application/web apps insecure??

I would like to use the same style.

|||

Hey

SQL concatenation techniques will result ini sql injection.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

You could take a look atSQL Injection for details.