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.
| Author |
Topic |
|
slickhead
Starting Member
5 Posts |
 Posted - 2005-10-04 : 13:11:58
|
| I am struggling to recode the following to work on SQL Svr...SQL = "SELECT DISTINCTROW First(PartHits.Number) AS NumberField, Count(PartHits.Number) AS NumberOfDups FROM PartHits WHERE PartHits.HitDate = #" & Request.Form("DailySearchDate") & "# GROUP BY PartHits.Number HAVING (((Count(PartHits.Number))>0)) ORDER BY Count(PartHits.Number) DESC"Help please. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-04 : 13:21:41
|
| Two problems:1) SQL Server does not recognize DISTINCTROW. Just use DISTINCT instead.2) SQL Server does not recognize FIRST. There's really no such thing as "first". You'll have to define what you really want (e.g. minimum IDENTITY number, earliest date, some other ORDER BY criteria). Then, you may have to implement an subquery, depending on the definition you pick.By the way, having a column name of "Number" is just begging for trouble. That's a reserved word. You may have to wrap that in square brackets to get it to work well.Finally, if what you're trying to do is eliminate duplicate rows, then read our FAQ.---------------------------EmeraldCityDomains.com |
 |
|
|
|
|
|
Substitute for DISTINCTROW in SQL Svr