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:
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_c3from
dbo
.t1group
byc1
go
drop
table dbo.t1go
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