SQL "FIRST" function

Hello michaelhenderson,

Do you mean something like:

Select Top 1 MyField From MyTable Order By MyOrderField

Regards,

TimCottee

I doubt this is what you mean but reading you question literally you are are looking for TOP. without specifiying an order by clause, you will get the first row in the referred to in the clustered index. if no CI, then you will get the very first row in the heap.

SELECT TOP 1 field_name FROM table_name

Select allOpen in new window

Thanks for the quick responses.

Basically I have a view (below).  I have a field I want to include and group by called "dbo.ViewDailyRework.DarRWC".  The problem is that I also want to run a query on this view which does some totals and a few calculations.  The select query I'm using displays one row (please see the attached results file)  The CompQty field is slightly out becuase there are a few records included in the dbo.ViewDailyRework.DarRWC but I only want the view to return the first record for that fied.

(I hope I'm making sense!)
_____
View:

SELECT     TOP (100) PERCENT dbo.ViewDailyCompleations.SecId, dbo.ViewDailyCompleations.Section, dbo.ViewDailyCompleations.FlnId,
                      dbo.ViewDailyCompleations.Flowline, dbo.ViewDailyCompleations.DasAssNo, dbo.ViewDailyCompleations.AssemblyDesc,
                      dbo.ViewDailyCompleations.DasDate, dbo.ViewDailyCompleations.DasOppNo, MAX(DISTINCT dbo.ViewDailyCompleations.DasCompQty) AS CompQty,
                      SUM(dbo.ViewDailyRework.DarQty) AS RwkQty, MAX(DISTINCT dbo.ViewDailyCompleations.OppCost) AS OppCost,
                      SUM(dbo.ViewDailyRework.FaultCost) AS FaultCost, dbo.ViewDailyRework.DarRWC
FROM         dbo.ViewDailyCompleations LEFT OUTER JOIN
                      dbo.ViewDailyRework ON dbo.ViewDailyCompleations.FlnId = dbo.ViewDailyRework.FlnId AND
                      dbo.ViewDailyCompleations.DasAssNo = dbo.ViewDailyRework.AssPartNo AND
                      dbo.ViewDailyCompleations.DasOppNo = dbo.ViewDailyRework.DarOppNo AND
                      dbo.ViewDailyCompleations.DasDate = dbo.ViewDailyRework.DarDate AND dbo.ViewDailyCompleations.SecId = dbo.ViewDailyRework.SecId
GROUP BY dbo.ViewDailyCompleations.FlnId, dbo.ViewDailyCompleations.DasAssNo, dbo.ViewDailyCompleations.DasDate,
                      dbo.ViewDailyCompleations.DasOppNo, dbo.ViewDailyCompleations.Flowline, dbo.ViewDailyCompleations.SecId, dbo.ViewDailyCompleations.Section,
                      dbo.ViewDailyCompleations.AssemblyDesc, dbo.ViewDailyRework.DarRWC
ORDER BY dbo.ViewDailyCompleations.SecId, dbo.ViewDailyCompleations.FlnId, dbo.ViewDailyCompleations.DasAssNo,
                      dbo.ViewDailyCompleations.DasOppNo, dbo.ViewDailyCompleations.DasDate

results.bmp

change your view to this...SQL Server doesn't have a "first" function like Access, but you can probably get away w/ using MIN()

SELECT     TOP (100) PERCENT dbo.ViewDailyCompleations.SecId, dbo.ViewDailyCompleations.Section, dbo.ViewDailyCompleations.FlnId,
                      dbo.ViewDailyCompleations.Flowline, dbo.ViewDailyCompleations.DasAssNo, dbo.ViewDailyCompleations.AssemblyDesc,
                      dbo.ViewDailyCompleations.DasDate, dbo.ViewDailyCompleations.DasOppNo, MAX(DISTINCT dbo.ViewDailyCompleations.DasCompQty) AS CompQty,
                      SUM(dbo.ViewDailyRework.DarQty) AS RwkQty, MAX(DISTINCT dbo.ViewDailyCompleations.OppCost) AS OppCost,
                      SUM(dbo.ViewDailyRework.FaultCost) AS FaultCost, DarRWC  = MIN(dbo.ViewDailyRework.DarRWC)
FROM         dbo.ViewDailyCompleations LEFT OUTER JOIN
                      dbo.ViewDailyRework ON dbo.ViewDailyCompleations.FlnId = dbo.ViewDailyRework.FlnId AND
                      dbo.ViewDailyCompleations.DasAssNo = dbo.ViewDailyRework.AssPartNo AND
                      dbo.ViewDailyCompleations.DasOppNo = dbo.ViewDailyRework.DarOppNo AND
                      dbo.ViewDailyCompleations.DasDate = dbo.ViewDailyRework.DarDate AND dbo.ViewDailyCompleations.SecId = dbo.ViewDailyRework.SecId
GROUP BY dbo.ViewDailyCompleations.FlnId, dbo.ViewDailyCompleations.DasAssNo, dbo.ViewDailyCompleations.DasDate,
                      dbo.ViewDailyCompleations.DasOppNo, dbo.ViewDailyCompleations.Flowline, dbo.ViewDailyCompleations.SecId, dbo.ViewDailyCompleations.Section,
                      dbo.ViewDailyCompleations.AssemblyDesc, dbo.ViewDailyRework.DarRWC
ORDER BY dbo.ViewDailyCompleations.SecId, dbo.ViewDailyCompleations.FlnId, dbo.ViewDailyCompleations.DasAssNo,
                      dbo.ViewDailyCompleations.DasOppNo, dbo.ViewDailyCompleations.DasDate

Hi

That's exactly what I needed.  Works great.  I had actually tried similar originally but got the syntax wrong as I didn't put the = symbol in!

Thanks.

Sweet...glad to help.

>>This has been dropped from SQL since SQL 2000. <<
Just a clarification.  FIRST has never existed in T-SQL.  You are confusing it with the MS Access SQL dialect.

The reason for that is that in SQL Server there is no concept of a "first record".  Everything is dependent on the ORDER BY clause.  Contrary to popular belief, but without an ORDER BY clause there is no gurantee of the results even when there is a clustered index.

Second do not use the TOP 100 PERCENT ... ORDER BY ruse or you will be disappointed when you migrate to 2005.  VIEWs should never have an ORDER BY clause. Period.

Ah. I see that you are already using SQL Server 2005, than you should expect turbulence.  Simply put SQL Server sees 100% and "optimizes" by ignoring the ORDER BY clause.

You will see any number of workarounds, from SELECT TOP 100 PERCENT to the latest favorite SELECT TOP 2147483647.  All of these are hacks and you should never rely on them.  But if you insist more power to you.