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
