SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Equivalent to MS Access'' "First" function


T-SQL Equivalent to MS Access'' "First" function

Author
Message
CGSJohnson
CGSJohnson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2763 Visits: 1686

Is there a SQL equivalent to MS Access' "first" function? Any help would be GREATLY appreciated. Thank you.

Chris


Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73103 Visits: 40960

the equivilent of the First() function is SELECT TOP 1 COLUMNNAME from sometable order by COLUMNNAME ;

the TOP function can give you the top 10 like david letterman, or return a percent of the values:

SELECT TOP 15 PERCENT * FROM SOMETABLE

without an explicit order by in your statement, it would be the first # of records, so make sure you always use an ORDER BY statement to get the results you want.



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Russel Loski
Russel Loski
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1770 Visits: 8907

This is fairly easy to write from a table. It is hard to describe in abstract.

I usually have to use a subquery embedded in a subquery that identifies uniquely the first record for a given set. One query finds the minimum (say the earliest date, or smallest number) for a given group by element. The next one subquery finds the smallest id for that group by element. The row that matches that id is the first for that group by element. Any value in that row is the "First" for that group by element.



Russel Loski, MCSE Business Intelligence, Data Platform
CGSJohnson
CGSJohnson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2763 Visits: 1686

I found this via a Google search:

SELECT Column1, column2

from table1

where column1 in (SELECT min(column1) from table1 group BY Column1)

It seems to work for my purpose. Any thoughts. Thanks.

Chris


Russel Loski
Russel Loski
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1770 Visits: 8907

The problem with this is that you have min(column1) and group by Column1. Think about it. This query is identical to (Select column1 from table1 group by Column1).

Try

SELECT Column1, column2

from table1 tmain

inner join

(SELECT column1, min(ID) as minID from table1 group BY Column1) a tfirst
on tmain.column1 = tfirst.column1 and ID = minID

The following order by column3:

Select t.column1, t.column2

from table1 t

inner join

(select min(ID) as ID, column1, MinColumn3

from table1 ttop

inner join (select column1, min(column3) as MinColumn3

from table1 group by column1) tmin

on tmin.column1 = ttop.column1 and MinColumn3 = Column3

group by column1, MinColumn3

) tfirst

on t.ID = tfirst.ID and t.Column1 = tfirst.Column1 and MinColumn3 = Column3



Russel Loski, MCSE Business Intelligence, Data Platform
Stephen Morley
Stephen Morley
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 239
The First() function in MSAccess returns the first record entered into the table.
SELECT TOP 1 is not the equivalent.

Try creating a new table in MSAccess with one string field. Enter in records B then A then C. If you then do a First() on this table you will get B. SELECT TOP 1 will return A if you order ascending and SELECT TOP 1 will return C is you order DESC therefore SELECT TOP 1 is not an equivalent no matter what ORDER BY you use.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29897 Visits: 19009
Stephen Morley (6/12/2008)
The First() function in MSAccess returns the first record entered into the table.
SELECT TOP 1 is not the equivalent.

Try creating a new table in MSAccess with one string field. Enter in records B then A then C. If you then do a First() on this table you will get B. SELECT TOP 1 will return A if you order ascending and SELECT TOP 1 will return C is you order DESC therefore SELECT TOP 1 is not an equivalent no matter what ORDER BY you use.



Close but no cigar. First will return the first value it HAPPENS to run into. in the qualifying set. Physical order is not guaranteed. Ultimately - unless an order is specified - it will return essentially any one of the values in the set.

From Access' own help:

The First and LastMoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.


In that sense - it's pretty close to what you'd get if you used TOP 1 with no specific order.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Stephen Morley
Stephen Morley
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 239
Sorry but i do not agree with you.

MS Access holds an internal bookmark on records in tables so it can determine the first record entered into that table. (I cannot locate the white paper I read this in at the moment but if i find it I'll pass it on).

If MS Access didn't do this then First() would return random results and having worked with MS Access the past 15 years in my experience it always returns the same result. Whats the point in having a function if its flakey?
Stephen Morley
Stephen Morley
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 239
Matt,

The help excerpt you published references a DAO recordset object. I am not using a recordset in my example - i am simply creating a query against a table.

Thanks anyway but no cigar for you either!

Regards
Steve
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73103 Visits: 40960
Stephen is kind of right, as long as you ignore some stuff.

unless you've added a primary key to the table, because Access is file based, Access really does keep the files in the physical order they were data entered.

In SQL,As soon as you index the table, or add a PK, the data will be reordered, and will vary from the original natural order. aCCESS JUST KEEPS POINTERS, SO THE FIRST() STILL RETURNS b. I think if you compact a database, the data might be reordered, but i'm not sure.

SQL Server will do the same thing, a heap table with no PK or indexes is still kept internally in the same order as it was data entered, just awaiting an ORDER BY Clause.

First() really is the same as the TOP 1 function, regardless.
As everyone was emphasizing a year ago, an ORDER BY clause is the best practice in order to assure consistant results.

Saying an order by is not necessary is just assuming that more advanced administration like adding indexes will never occur. on simple databases, that's probably true, but that doesn't mean there is not a better way to do it.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search