SQL Query Question: Get the first row for each Acc

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

SQL Server Development (2000)

SQL Query Question: Get the first row for each Acc

Author

Topic

hnomani
Starting Member

35 Posts

Posted - 2002-01-29 : 11:44:04

I have two tables for example,

CREATE TABLE TABLE1 (ACCT_NBR INT)
GO
CREATE TABLE TABLE2 (ACCT_NBR INT, AMT NUMERIC(9,2))
GO
INSERT INTO TABLE1 (ACCT_NBR) VALUES (100)
GO
INSERT INTO TABLE2 (ACCT_NBR, AMT) VALUES (100, 11.00)
GO
INSERT INTO TABLE2 (ACCT_NBR, AMT) VALUES (100, 10.14)
GO
INSERT INTO TABLE2 (ACCT_NBR, AMT) VALUES (100, 11.55)
GO

ACCT_NBR can be used to join TABLE1 and TABLE2. How would I write a query to get the FIRST row values from TABLE2. TABLE2 does not have any unique column and aggregate functions such as MIN or MAX may not necessary result in the return of the FIRST matching row on ACCT_NBR. The result set should get ACCT_NBR = 100, AMT = 11.00 value.

Is it possible to get the first row from the TABLE2 for each acct_nbr and how? This is just a sample table of the system I am using, we are using SQL Server 2000.

Thanks
Haseeb

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-29 : 11:58:47

There is no 'first' row for each account number in TABLE2.

If you have created TABLE2 from a source where such order is meaningful, then by putting it into that table, you have lost that ordering. If you are populating TABLE2 directly, you are mistaken in believing that there is any ordering relationship between the rows independent of the data they contain.

Edited by - Arnold Fribble on 01/29/2002 12:01:46

hnomani
Starting Member

35 Posts

Posted - 2002-01-29 : 14:40:29

Thanks for the reply. We are currently using a file based reporting system that has a function named FIRST. It takes the first row it finds a match on.
I wanted to make sure that there is no thing as such in SQL Server. The basic Relational Database principle does not specify any physical order of the data stored and it make sense that there is no FIRST function in SQL Server.

Thanks Again,
Haseeb

quote:

There is no 'first' row for each account number in TABLE2.

If you have created TABLE2 from a source where such order is meaningful, then by putting it into that table, you have lost that ordering. If you are populating TABLE2 directly, you are mistaken in believing that there is any ordering relationship between the rows independent of the data they contain.

Edited by - Arnold Fribble on 01/29/2002 12:01:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources