'First' not supported in SQL Server

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

SQL Server 2005 Forums

Transact-SQL (2005)

'First' not supported in SQL Server

Author

Topic

abhijeetdighe
Starting Member

24 Posts

Posted - 2007-09-14 : 01:19:07

I am using following query in access

Select first(fname),age from mytab group by age

Database :

Sam 21
Raj 21
Karan 21
John 23
Rocky 23

O/P of Query :

Sam 21
John 23

I want to use the same query in SQL Server 2005,But First keyword is not supported in SQL Server

2005.So how can I write my own function 'first' in SQL Server 2005, so that I will not need to change

above query in SQL Server also and it should give same result.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 02:00:20

There is no "First" supported in SQL Server. Try this

Select firstname,age from mytab t
where firstname=(select top 1 firstname from mytab where age=t.age)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 02:05:25

SELECT Name, Age FROM (
SELECT Name, Age, ROW_NUMBER() OVER (PARTITION BY Age ORDER BY Name) AS RecID FROM MyTab
) AS d WHERE RecID = 1

SELECT Age, MIN(Age) FROM MyTab GROUP BY Age

E 12°55'05.25"
N 56°04'39.16"

abhijeetdighe
Starting Member

24 Posts

Posted - 2007-09-14 : 02:27:20

But I want to use the same query in SQL Server that works in MS Access.
Is it possible to write User defined function in SQL Server, which will work just like 'First' in Access.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 02:43:00

quote:
Originally posted by Peso

SELECT Name, Age FROM (
SELECT Name, Age, ROW_NUMBER() OVER (PARTITION BY Age ORDER BY Name) AS RecID FROM MyTab
) AS d WHERE RecID = 1

SELECT Age, MIN(~~Age~~ firstname) FROM MyTab GROUP BY Age

E 12°55'05.25"
N 56°04'39.16"

I didnt notice this is posted at 2005 forum

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 02:44:40

quote:
Originally posted by abhijeetdighe

But I want to use the same query in SQL Server that works in MS Access.
Is it possible to write User defined function in SQL Server, which will work just like 'First' in Access.

Whu are you trying to simulate the same in sql server?

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-14 : 03:12:11

you still need to convert some commands
order by may not work too...

it's either karan and john
or sam and rocky

unless you have a column for the id?

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 03:13:00

The correction made by Madhi will work in both Access and SQL Server.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 04:44:00

quote:
Originally posted by jen

you still need to convert some commands
order by may not work too...

it's either karan and john
or sam and rocky

unless you have a column for the id?

--------------------
keeping it simple...

The query I suggested wont need any order by


declare @t table(firstname varchar(20), age int)
insert into @t
select 'Sam', 21 union all
select 'Raj', 21 union all
select 'Karan', 21 union all
select 'John', 23 union all
select 'Rocky', 23

Select firstname,age from @t t
where firstname=(select top 1 firstname from @t where age=t.age)

Result


firstname            age         
-------------------- ----------- 
Sam                  21
John                 23

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-14 : 04:46:38

but to answer your original question. no, you can't write that kind of function.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 04:56:28

quote:
Originally posted by abhijeetdighe

But I want to use the same query in SQL Server that works in MS Access.

I hope the query I suggested would work in ACCESS as well. Test it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources