trying to find a replacemnt for the First function

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 2000 Forums

Transact-SQL (2000)

trying to find a replacemnt for the First function

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-01-31 : 20:37:51

Hi,
I am converting an Ms Access databse to a SQL Server 2005 databse. And one of the queries that I am converting has the First function around a field. I never saw this function before, but from what I can tell, it sorts the column and displase the fist item in the sorted field.
I am not sure how I would do this in SQL Server. Any ideas would be really appreacite it.

ITM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-31 : 21:22:13

You can use:

Select Top 1 columnnames order by columnnames(your criteria)

Show us the query. Someone will help you.

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-01-31 : 21:52:13

This is the query when I try top it gives me a error (but just telling nothing but there is an syntex error near Top) Am I missing something here?

ITM

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-01-31 : 21:52:53

the query: SELECT [Start of Business].GroupID, [Start of Business].Rep,
Top([[Start of Business].[Group]) AS StartingGroup
FROM [Start of Business]

ITM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-31 : 22:04:19

Not sure if this will display the correct results as you haven't provided a data example, but try this:

SELECT GroupID, Rep, Group AS StartingGroup
FROM [Start of Business]
GROUP BY GroupID, Rep

If that doesn't return the correct result set, then we'll need to see a data example that shows the before and after data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-31 : 23:01:40

Maybe:

SELECT GroupID, Rep, Group AS StartingGroup
FROM [Start of Business]
Where Group = (Select TOP 1 Group from [Start of Business])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 01:08:56

quote:
Originally posted by tkizer

Not sure if this will display the correct results as you haven't provided a data example, but try this:

SELECT GroupID, Rep, Group AS StartingGroup
FROM [Start of Business]
GROUP BY GroupID, Rep

If that doesn't return the correct result set, then we'll need to see a data example that shows the before and after data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

you require some kind of aggregation (MIN,MAX,..) to be applied to Group field or else it will throw syntax error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-01 : 01:13:02

Yes. It was a typo obviously.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-01 : 01:13:32

SELECT GroupID, Rep, MAX(Group) AS StartingGroup
FROM [Start of Business]
GROUP BY GroupID, Rep

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources