What is the transact sql equivilant

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)

What is the transact sql equivilant

Author

Topic

MrWombat
Starting Member

11 Posts

Posted - 2003-07-16 : 00:43:47

Hi i am a bit new to Transact SQL and was wondering what the equivilant to the ACCESS FIRST() function is.
I have it used in the access as so
SELECT tbl_Estates.est_Id,
tbl_Estates.est_Name,
tbl_Contracts.ctr_Stage,
tbl_Contracts.ctr_Street,
tbl_Contracts.ctr_SubStage,
tbl_Contracts.ctr_FileID,
tbl_Contracts.ctr_LotNumber,
tbl_Contracts.ctr_ActualPrice, First(tbl_Contracts.ctr_PurchaserAddressee) AS FirstOfctr_PurchaserAddressee,
First(tbl_Purchasers.pur_Address1) AS FirstOfpur_Address1,
First(tbl_Purchasers.pur_Address2) AS FirstOfpur_Address2,
First(tbl_Purchasers.pur_Suburb) AS FirstOfpur_Suburb,
First(tbl_Purchasers.pur_State) AS FirstOfpur_State,
First(tbl_Purchasers.pur_Postcode) AS FirstOfpur_Postcode,
tbl_Contracts.ctr_ContractStatus,
tbl_Contracts.ctr_ActualSettlement,
tbl_Contracts.ctr_DateFallenOver,

FROM (tbl_ContractStatus INNER JOIN (tbl_Contracts INNER JOIN tbl_Estates
ON tbl_Contracts.ctr_Estate = tbl_Estates.est_Id)
ON tbl_ContractStatus.sta_Id = tbl_Contracts.ctr_ContractStatus) INNER JOIN tbl_Purchasers
ON tbl_Contracts.ctr_FileID = tbl_Purchasers.pur_FileId

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-16 : 02:15:01

You may use MIN() or MAX() aggregate functions instead:

SELECT MIN(au_lname)
FROM authors

Here is the result set:

--------------------

Bennet

- Vit

MrWombat
Starting Member

11 Posts

Posted - 2003-07-16 : 02:24:46

Thanks. But i have tried that and unfortunetly there are duplicate purchasers for certain contracts and they still are both shown in the results. But all i really want is the first purchasers details. As both purchasers details are the same. Any other ideas anyone????

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-16 : 03:22:04

quote:
there are duplicate purchasers for certain contracts and they still are both shown in the results

You mean now you get this:


1      John F.      West Boulevard, 43
2      John F.      West Boulevard, 43

and you want like this:

1      John F.      West Boulevard, 43   ???

Edited by - Stoad on 07/16/2003 06:00:15

MrWombat
Starting Member

11 Posts

Posted - 2003-07-16 : 05:22:19

Nope that just doesnt return the same results.

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-16 : 06:09:12

Have you tried SELECT DISTINCT ?

-------
Moo. :)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 10:07:59

Wombat-
The FIRST() function is Access is not ANSI SQL compliant, and therefore you will not see this function implemented in any major RDBMS - SQL Server, Oracle, DB2, etc. It isn't compliant because it allows ambiguity in which row gets returned as the "first" row.

Access users don't see this ambiguity because Access makes row position meaningful. This violates the relational model, and, again, is not supported by any major RDBMS'.

You will need a combination of MIN() or MAX() and an GROUP BY to write an ANSI SQL equivalent.

Edited by - setbasedisthetruepath on 07/16/2003 10:08:19

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-16 : 11:24:06

quote:

Access users don't see this ambiguity because Access makes row position meaningful. This violates the relational model, and, again, is not supported by any major RDBMS'.

Actually, Access if every bit as relational as SQL Server. Nothing about Access forces users to abandon relational database theory.

It just makes it a little too easy sometimes .... ("hey, before you save this table, can I add an autonumber field for you? no? are you sure? why not? please ......")

- Jeff

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 11:48:15

Perhaps it's a semantic difference. Access doesn't support the ANSI SQL standard, so to my mind it can't be called a relational product. Otherwise, what does the term "relational product" mean?

Meaningful row position does violate the relational model, as does FIRST(). It is true that nothing forces you to use it. But there are ANSI constructs that you cannot use in Access, like SELECT COUNT( DISTINCT {column} ).

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 15:51:04

Well, I guess this is an interesting question..(at least to me)

Since you can connect Access to SQL Server and use the FIRST() command, how is it interpreted by SQL Server?

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 16:43:43

In that scenario SQL Server is just the data provider; the optimization is done by the Jet engine (Access). It's only with "passthru" queries that a raw SQL statement is being sent to SQL server for evaluation and rowset return.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 10:33:32

In other words, SQL will do a simple select and return all of the data for each group?

The jet figures out which one is first?

How effecient!

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-17 : 10:51:55

Jet is actually quite good (in most cases, certainly not all) at sending down criteria and grouping to linked tables, if it can. When you use ODBC, there are only a limited number of functions or aggregate functions that you can use, and Access does a nice job if you say:

select left(name,2), COUNT(*)
from table
group by left(name,2)

of passing the SQL to the source and returning only the needed results. However, if you say:

select SomeCustomFunction(name), count(*)
from table
group by SomeCustomFunction(name)

then access must of course return ALL rows from the source. same with the FIRST() aggregation; it has no choice ... it must process each row.

if you are careful and tinker a little, you might be surprised at how well you can send grouping and criteria and how efficiently Jet uses linked tables. Not perfect by any means, but I feel quite good ... something JET never really gets enough credit for.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-17 : 18:09:19

Let me pass a couple of words on this subject.
Jonathan wrote:
Meaningful row position does violate the relational model, as does FIRST().

It does not!!

select first(n), first(m1), first(m2), first(m3) from t;
is equal to:
select top 1 n, m1, m2, m3 from t

In this case 'First' is just a word, it might be named e.g. 'SuperTop'.

The same case with following:

select n, first(m1), first(m2), first(m3) from t
group by n;

it's something like this fancy select:

select n, (select top 1 m1, m2, m3) from t
group by n

i.e. on random basis Jet selects top 1 from the subset of fields but only
inside of each group.

BTW, how it can be done in T-SQL (in the simplest way I mean)?

- Vit

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-17 : 18:28:46

Uh Oh Vit! I feel another one of those lengthy (but very informative) threads coming on.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-18 : 03:24:01

Tara.

Why? Something wrong? :)

Once again, to say that First() violates R. approach is equal
to say that "select top 1 ... from ... order by ..." does it too.

This function just can select "top 1" within each single group.

Obviously the TOP instruction might be also named FIRST if
we prefer meaningful names...

- Vit

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-18 : 09:28:25

Stoad -- I hear you. and mostly agree.

When people talk about row ordering and how it means nothing in RDMS's, I usually ask them about the TOP operator and how that plays into things, they usually get upset at me.

They tend to get confused: how the rows are STORED means nothing (except for performance factors), but how they are RETURNED in a given query might be quite important.

FIRST() and LAST() are actually useful -- but in only two cases I can think of:

1) In a MS Access report, you might have:

CUSTOMER Header
DETAIL
CUSTOMER Footer

if the detail is ordered by, say, "Date", then sometimes it is useful to put in the group header or footer FIRST(OrderID) -- which return first OrderID listed on the report for that customer. This is the ONLY time in Access I ever use FIRST() or LAST(). But this is more of a report display/design issue as opposed to a data processing issue.

2) if a sub-SELECT is ORDERED (which is not allowed in SQL unless you "fake" it with the top 100% trick) then the FIRST() or LAST() aggregate functions would actually be VERY useful:

select employee, last(salary) as LatestSalary
from
(select customer, salary
from salaries
order by employee, year, month
) a
group by employee

the key to the above is you are ordering by TWO columns (year,month) which is really a pain to do by normal SQL methods. Of course, I'm not advocating setting up a table in which year and month are seperate fields or anything like that, it's just an example.

And, of course, a disclaimer: i'm not complaining that SQL Server doesn't have a FIRST() or LAST() aggregate function, nor do I think it should, I'm just mentioning that a function like that can be useful potentially.

- Jeff

Edited by - jsmith8858 on 07/18/2003 09:36:14

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 09:46:24

I think Tara's right...we're in for a long one...

quote:

select first(n), first(m1), first(m2), first(m3) from t;
is equal to:
select top 1 n, m1, m2, m3 from t

ABS(NOT)

how data is stored in a db should not have any bearing on anything..

breaks one of codds 10 rules...now where did I put those...

Ah yes..

http://engr.smu.edu/~fmoore/notes/12rules.htm

I believe it's rule numero uno

as an aside, does SQL Server break rule 4?

While it does have a catalog...MS had to come up with complex INFROMATION_SCHEMA views...

so how is that "self describing"?

AND, they don't have views that cover everything...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-18 : 10:36:10

Brett missed my post, aparently.

Stoad is 100% correct. the two are equivalent.

he is not talking about how data is stored. no one is. he is talking about how data is returned in a query.

if you are using the TOP operator, hopefully you can understand that the order in which data is returned in a query (not stored in table) IS important.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 11:09:53

quote:

Brett missed my post, aparently.

Yeah, I thought I refreshed.

quote:

Stoad is 100% correct. the two are equivalent.

[moo]I respectfulyy disagree[/moo]

quote:

he is not talking about how data is stored. no one is. he is talking about how data is returned in a query.

quote:

if you are using the TOP operator, hopefully you can understand that the order in which data is returned in a query (not stored in table) IS important.

OK...

never did like TOP anyway..it's like saying the other rows don't matter

AND if TOP n and FIRST() where the same, why didn't MS put FIRST in T-SQL lexicon?

TOP (without any ordering) will produce unpredicatable reults.

Does FIRST(). I think FIRST() will always be predictable. And why is that?

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-18 : 11:51:36

Thank you, Jeff. You've given just a brilliant explanation on everything.

quote:
if you are using the TOP operator, hopefully you can understand that
the order in which data is returned in a query (not stored in table) IS important

it should be write into handbooks as the 1st amendment of the 10 codd's rules.

- Vit

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources