Is there a SQL equivalent to MS Access' "first" function? Any help would be GREATLY appreciated. Thank you.
the equivilent of the First() function is SELECT TOP 1 COLUMNNAME from sometable order by COLUMNNAME ;
the TOP function can give you the top 10 like david letterman, or return a percent of the values:
SELECT TOP 15 PERCENT * FROM SOMETABLE
without an explicit order by in your statement, it would be the first # of records, so make sure you always use an ORDER BY statement to get the results you want.
This is fairly easy to write from a table. It is hard to describe in abstract.
I usually have to use a subquery embedded in a subquery that identifies uniquely the first record for a given set. One query finds the minimum (say the earliest date, or smallest number) for a given group by element. The next one subquery finds the smallest id for that group by element. The row that matches that id is the first for that group by element. Any value in that row is the "First" for that group by element.
I found this via a Google search:
SELECT Column1, column2
where column1 in (SELECT min(column1) from table1 group BY Column1)
It seems to work for my purpose. Any thoughts. Thanks.
The problem with this is that you have min(column1) and group by Column1. Think about it. This query is identical to (Select column1 from table1 group by Column1).
from table1 tmain
(SELECT column1, min(ID) as minID from table1 group BY Column1) a tfirston tmain.column1 = tfirst.column1 and ID = minID
The following order by column3:
Select t.column1, t.column2
from table1 t
(select min(ID) as ID, column1, MinColumn3
from table1 ttop
inner join (select column1, min(column3) as MinColumn3
from table1 group by column1) tmin
on tmin.column1 = ttop.column1 and MinColumn3 = Column3
group by column1, MinColumn3
on t.ID = tfirst.ID and t.Column1 = tfirst.Column1 and MinColumn3 = Column3