Selecting First records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Selecting First records

Author

Topic

edmyster
Starting Member

2 Posts

Posted - 2010-01-14 : 16:07:14

Hi,

I am transferring Access queries to SQL and am just having some problems with keywords in Access being translated to SQL. The issue is with the First keywaord in Access.

I have a table in SQL Server with a composite PK. They table is called UserFirm and the PK is made up of the UserID and FirmID. I need to select the distinct UserID and the first FirmID that the use belongs to eg...

UserID FirmID
Uxxx001 Fxxx001
Uxxx002 Fxxx001
Uxxx002 Fxxx002
Uxxx003 Fxxx002
Uxxx004 Fxxx003

so the result set i want returned is
UserID FirmID
Uxxx001 Fxxx001
Uxxx002 Fxxx001
Uxxx003 Fxxx002
Uxxx004 Fxxx003

Access uses the following SQL to do this:
SELECT UserID, First(firm_id) AS Firm_id
FROM UserFirm

Just wondering if anybody knows the SQL equivalent

cheers....

Forgot to add using SQL 2005

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-14 : 16:13:19

MIN??

select UserID, min(FirmID) from <urtable> group by UserID

edmyster
Starting Member

2 Posts

Posted - 2010-01-14 : 16:24:46

Cheers, seems to work a treat!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources