Analogous to First function in T-SQL

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)

Analogous to First function in T-SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-25 : 09:45:29

Ingreet writes "I need to return first record in the designated field.
It can easily be done through Access First function.
How can I get the same result with T-SQL?
Here is the example of the data in the CUSTOMER table:

 ACCOUNT     STATUS      BTNAME
---------------------------------
   BEL10        A          BELK STORES SERVICES
   BEL10        A          BELK
   BEL10        X          METHEW BELK
   BEL10        A          BELK-MONROE MALL
   BEL10        A          BELK BERRY
   CAT10    (new account info follows)

SELECT CUSTOMER.ACCOUNT, First(CUSTOMER.STATUS) AS FirstOfSTATUS, First(CUSTOMER.BTNAME) AS FirstOfBTNAME
FROM CUSTOMER
WHERE (((CUSTOMER.BTNAME)<>" "))
GROUP BY CUSTOMER.ACCOUNT;"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-25 : 10:03:58

There is no concept of FIRST in relational theory (the fundation for SQL). That is because there is no concept of natural row order.

So the question is FIRST, by what order?

<O>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-07-25 : 14:29:23

And once you've decided the order, then you might want to look into methods that use SELECT TOP 1...

Garth
SQLTeam Author

119 Posts

Posted - 2002-07-25 : 19:58:27

I'm going to take a guess as to what you are looking to accomplish...

SET NOCOUNT ON

CREATE TABLE Accounts
(
AC_ID smallint IDENTITY,
Account varchar(10),
Status char(1),
BtName varchar(30)
)
go
INSERT Accounts VALUES ('BEL10','A','BELK STORES SERVICES')
INSERT Accounts VALUES ('BEL10','A','BELK')
INSERT Accounts VALUES ('BEL10','A','BELK-MONROE MALL')
INSERT Accounts VALUES ('CAT10','A','CAT10 STORES SERVICES')
INSERT Accounts VALUES ('CAT10','A','CAT10')
INSERT Accounts VALUES ('CAT10','A','CAT10-MONROE MALL')

SELECT *
FROM Accounts a
WHERE AC_ID=(SELECT MIN(AC_ID) FROM Accounts b WHERE a.Account=b.Account)

-- Results --

AC_ID Account Status BtName
------ ---------- ------ ------------------------------
1 BEL10 A BELK STORES SERVICES
4 CAT10 A CAT10 STORES SERVICES

Garth
www.SQLBook.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources