Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FIRST Function in SQL Server 2005????

Status
Not open for further replies.
dandot

dandot

Programmer
Jul 19, 2005
53
CA
Is the first function available in sql server 2005?

I am gettin the following error:
Msg 195, Level 15, State 10, Line 7
'FIRST' is not a recognized built-in function name.


Here is the query:
Select CONVERT(VARCHAR(12), FactPanelHistoryAll.FinalSimulatorReadTime,101) As Date,
AVG (FactPanelHistoryAll.EfficiencyTotal) As AverageEfficiency,
STDEV (EfficiencyTotal) As StDev,
--AVG (FactPanelHistoryAll.EfficiencyTotal)- STDEV (FactHistoryAll.EfficiencyTotal) as Var1,
--AVG (FactPanelHistoryAll.EfficiencyTotal)+ STDEV (FactHistoryAll.EfficiencyTotal) as Var2,
Count(SubID) as Volume,
--First (#tempEfficiencyTarget.EfficiencyTarget) As EfficiencyTarget,
--FIRST(#tempEfficiencyTarget.VolumeTarget) As VolumeTarget

From FactPanelHistoryAll, DimTime, #tempEfficiencyTarget

Where FactPanelHistoryAll.[FillFactor] > 0 And FactPanelHistoryAll.[FillFactor] <100
And FactPanelHistoryAll.Isc > 1
And Convert(VARCHAR(12), FactPanelHistoryAll.FinalSimulatorReadTime, 102) >= '2007.01.01' AND Convert(VARCHAR(12), FactPanelHistoryAll.FinalSimulatorReadTime, 102) <= '2008.01.01'
And Convert(VARCHAR(12), FactPanelHistoryAll.FinalSimulatorReadTime, 102) = Convert (VARCHAR(12), DimTime.CalendarDate,102)
And DimTime.Fiscalyear = #tempEfficiencyTarget.FiscalYear
And DimTime.FiscalMonth = #tempEfficiencyTarget.FiscalMonth
GROUP BY CONVERT(VARCHAR(12), FactPanelHistoryAll.FinalSimulatorReadTime,101)
Order By Date
 
Sort by date Sort by votes
sorry... ignore the commenting out of hte first lines, was experimenting with the query :)
 
Upvote 0 Downvote
I don't believe sql server 2005 supports FIRST. You would have to use something like TOP.


Code: 
[COLOR=blue]Select[/color] [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]VARCHAR[/color](12), FactPanelHistoryAll.FinalSimulatorReadTime,101) [COLOR=blue]As[/color] [COLOR=blue]Date[/color],
[COLOR=#FF00FF]AVG[/color] (FactPanelHistoryAll.EfficiencyTotal) [COLOR=blue]As[/color] AverageEfficiency,
STDEV (EfficiencyTotal) [COLOR=blue]As[/color] StDev,
[COLOR=#FF00FF]AVG[/color] (FactPanelHistoryAll.EfficiencyTotal)- STDEV (FactHistoryAll.EfficiencyTotal) [COLOR=blue]as[/color] Var1,
[COLOR=#FF00FF]AVG[/color] (FactPanelHistoryAll.EfficiencyTotal)+ STDEV (FactHistoryAll.EfficiencyTotal) [COLOR=blue]as[/color] Var2,
[COLOR=#FF00FF]Count[/color](SubID) [COLOR=blue]as[/color] Volume,
([COLOR=blue]Select[/color] top 1 (#tempEfficiencyTarget.EfficiencyTarget)) [COLOR=blue]As[/color] EfficiencyTarget,
([COLOR=blue]Select[/color] top 1(#tempEfficiencyTarget.VolumeTarget)) [COLOR=blue]As[/color] VolumeTarget

[COLOR=blue]From[/color] FactPanelHistoryAll, DimTime, #tempEfficiencyTarget

[COLOR=blue]Where[/color] FactPanelHistoryAll.[[COLOR=blue]FillFactor[/color]] > 0 And FactPanelHistoryAll.[[COLOR=blue]FillFactor[/color]] <100
And FactPanelHistoryAll.Isc > 1
And [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VARCHAR[/color](12), FactPanelHistoryAll.FinalSimulatorReadTime, 102) >= [COLOR=red]'2007.01.01'[/color] AND [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VARCHAR[/color](12), FactPanelHistoryAll.FinalSimulatorReadTime, 102) <= [COLOR=red]'2008.01.01'[/color]
And [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VARCHAR[/color](12), FactPanelHistoryAll.FinalSimulatorReadTime, 102) = [COLOR=#FF00FF]Convert[/color] ([COLOR=blue]VARCHAR[/color](12), DimTime.CalendarDate,102)
And DimTime.Fiscalyear = #tempEfficiencyTarget.FiscalYear
And DimTime.FiscalMonth = #tempEfficiencyTarget.FiscalMonth
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]VARCHAR[/color](12), FactPanelHistoryAll.FinalSimulatorReadTime,101)
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] [COLOR=blue]Date[/color]


Well Done is better than well said
- Ben Franklin
 
Upvote 0 Downvote
The FIRST function is not ANSI.

Google "SQL First Function" for some interesting discussion about its merits.



Phil Hegedusich
Senior Programmer/Analyst
IIMAK
-----------
Not NULL-terminated yet.
 
Upvote 0 Downvote
dandot

I haven't used FIRST since my MS Access days, but I made an error.

You would need to use MAX or MIN in sql server.

To get the first or smallest number:
MIN(#tempEfficiencyTarget.EfficiencyTarget)

The LAST or biggest number
MAX(#tempEfficiencyTarget.EfficiencyTarget)


Well Done is better than well said
- Ben Franklin
 
Upvote 0 Downvote
I don't believe CONVERT is either [ponder]

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
383
Volvere
V
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
372
pjw001
pjw001
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
284
SaltyTheFrog
SaltyTheFrog
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
505
mikrom
mikrom
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
246
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top