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
 Other Forums
 MS Access
 First Function

Author  Topic 

jeff
Starting Member

6 Posts

Posted - 2003-03-28 : 17:04:57
Hi,

Can you help me write a generic User-defined function in SQL2002 that have FieldName and TableName as parameters and returns only the Top record of the table. The function should work like the First() function of MSAccess.

Hope you can help me in this one.

Thanks,
Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-28 : 17:09:31
Why not just:


SELECT TOP 1 FieldName FROM TableName ORDER BY ... something ....


note that you need to order your table by some field(s); what is your definition of the "top" record?



- Jeff
Go to Top of Page

jeff
Starting Member

6 Posts

Posted - 2003-03-31 : 09:29:32
Hi,

Can you help me write/create a User-Defined-Function in SQLServer2000 Database that works like the FIrst Function of Access? The parameters to be passed to the function should be FieldName and TableName. It seems that tablename cannot be a variable.


To be used for SQL Statement like :
select ID, Name, MyFirst('SubjectName', 'Subjects') from Student


Example:
CREATE FUNCTION dbo.MyFirst
(@FieldName varchar(100), @TableName varchar(100))
RETURNS Table AS
Return
(
select top 1 @FieldName from @TableName
)



Thanks Again,
Jeff
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-31 : 10:08:35
Jeff - it is important to understand that FIRST() has NO MEANING unless you are ORDERING your table by fields ... does that make sense? SQL does not store the data or return it in any particular order, so

"give me the first row"

makes no sense ... it will be a random record. if you say

"give me the first row, ordering the table by trans_date"

then that makes sense, and you will get consistent results.

I hope this gives you some guidance.


- Jeff
Go to Top of Page
   

- Advertisement -