Conversion problem (SQL syntax)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conversion problem (SQL syntax)

Author

Topic

shyamprasad76
Starting Member

38 Posts

Posted - 2001-12-31 : 11:59:15

Hi,

I am doing a conversion project from Msft Access to SQL Server 2000. I converted most of the Access transact Sql syntax to Sql Server 2000. However I had problems with a query for which I need help in writing the transact SQL syntax that works in SQL server 2000.

Access syntax:

SELECT DISTINCTROW [MonthPar] From TimePeriod GROUP BY [MonthPar] HAVING (((Count ([MonthPar]))>=1)) ORDER BY Min ([DateMem]);")

I know the substitute for DISTINCTROW in SQL server is DISTINCT.
But Sql Server does not allow different field in the ‘ORDER BY’ Clause if DISTINCT is used after the ‘SELECT’.

But in the above query I require to GROUP by [MonthPar] and ORDER by [DateMem] field. Please let me know how to achieve this through transact SQL in SQL server 2000.

Thanks in advance,
Shyam.

shyamprasad76
Starting Member

38 Posts

Posted - 2001-12-31 : 12:54:51

Hi,

Please let me know if i am not clear in explaining the problem.

Thanks,

Shyam.

izaltsman
A custom title

1139 Posts

Posted - 2001-12-31 : 13:45:34

Actually (although I could be wrong) I don't think DISTINCTROW makes any difference in that query. See if the following brings back the same result (should work in both Access and SQL Server):


SELECT  [MonthPar] 
From TimePeriod 
GROUP BY [MonthPar] 
HAVING Count ([MonthPar])>=1 
ORDER BY Min ([DateMem])

shyamprasad76
Starting Member

38 Posts

Posted - 2001-12-31 : 14:05:08

Thank You.

Actually i thought what is more than required. When there is "Having [MonthPar]> 1" then there is no need of DISTINCT. So, when there is No DISTINCT , i can order it by any field that i want.

Thanks,
Shyam.

quote:

Actually (although I could be wrong) I don't think DISTINCTROW makes any difference in that query. See if the following brings back the same result (should work in both Access and SQL Server):

SELECT [MonthPar]
From TimePeriod
GROUP BY [MonthPar]
HAVING Count ([MonthPar])>=1
ORDER BY Min ([DateMem])

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2001-12-31 : 14:17:57

Actually using the GROUP BY causes it to act like DISTINCT. And while on this topic, I have read somewhere that DISTINCT can be slow?! Would GROUP BY be faster? I'd like to see some stats on those ;-)

- Onamuji

shyamprasad76
Starting Member

38 Posts

Posted - 2001-12-31 : 16:40:43

I am sorry, when i run the below statement it comes up with an error saying that:

the [DateMem] is invalid in the ORDER BY Clause because it is not contained in either an aggregate function or the GROUP BY clause.

Please suggest me if there is a way to do this.

Thanks,

Shyam.

quote:

Actually (although I could be wrong) I don't think DISTINCTROW makes any difference in that query. See if the following brings back the same result (should work in both Access and SQL Server):

SELECT [MonthPar]
From TimePeriod
GROUP BY [MonthPar]
HAVING Count ([MonthPar])>=1
ORDER BY Min ([DateMem])

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-31 : 23:13:36

Hi

Put [DateMem] in your group by clause, and give it another try.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources