Migrate from Access queries to T-SQL

Could anyone help me translate an access statement to T-SQL?

1. what is the Equivalent of First() in T-SQL?

need to convert:

SELECT NAMAST.SSN_9, Sum(MASTER_DSC.DSC_CUR_BAL_21) AS SumOfDSC_CUR_BAL_21, First(MASTER_DSC.ACCTNO) AS FirstOfACCTNO
FROM MASTER

Thanks in advance for your help or advice...

FIRST or LAST based on what criteria?.

Guessing by the statement posted, you could try using aggregate function MIN.

Example:

Code Snippet

create table dbo.t1 (

c1 int not null,

c2 int not null,

c3 money not null

)

go

insert into dbo.t1 values(1, 1, 5)

insert into dbo.t1 values(1, 2, 5)

insert into dbo.t1 values(2, 10, 5)

insert into dbo.t1 values(2, 20, 5)

insert into dbo.t1 values(2, 30, 3)

go

select

c1,

min(c2) as min_c2,

sum(c3) as sum_c3

from

dbo.t1

group by

c1

go

drop table dbo.t1

go

AMB

|||

Would the full statement help anyone help me?

SELECT NAMAST.SSN_9
, Sum(MASTER_DSC.DSC_CUR_BAL_21) AS SumOfDSC_CUR_BAL_21
, First(MASTER_DSC.ACCTNO) AS FirstOfACCTNO
FROM MASTER_DSC
INNER JOIN NAMAST ON (MASTER_DSC.APP=NAMAST.APP) AND (MASTER_DSC.ACCTNO=NAMAST.ACCTNO)
WHERE (((NAMAST.SEQ)=0) AND ((MASTER_DSC.DSC_STATUS) Not In (9,10,11,90)))
GROUP BY NAMAST.SSN_9
HAVING (((NAMAST.SSN_9)>"0") AND ((Sum(MASTER_DSC.DSC_CUR_BAL_21))>=[Total Customer Deposits >= What $ Amount]))
ORDER BY NAMAST.SSN_9;

|||

I think "First" is a "group operator" in Access, causing it to grab the value from the first record it encounters, without any regard to order. So it is potentially rather vague in what it means, since the data may not always be "grabbed" in the same order.

You are probably just as well off to use something like MIN or MAX.

Dan

|||Thanks hunchback and DanR1. You've been a great help!