Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-SQL Equivalent to MS Access'' "First" function Expand / Collapse
Author
Message
Posted Tuesday, March 27, 2007 11:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:15 AM
Points: 1,564, Visits: 1,342

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

Chris

Post #354263
Posted Tuesday, March 27, 2007 12:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 12,910, Visits: 32,013

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #354301
Posted Tuesday, March 27, 2007 5:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:23 AM
Points: 565, Visits: 8,705

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, MCITP
Post #354350
Posted Wednesday, March 28, 2007 7:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:15 AM
Points: 1,564, Visits: 1,342

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

Post #354477
Posted Wednesday, March 28, 2007 8:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:23 AM
Points: 565, Visits: 8,705

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, MCITP
Post #354502
Posted Thursday, June 12, 2008 7:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 64, Visits: 233
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.
Post #516352
Posted Thursday, June 12, 2008 7:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,139, Visits: 15,188
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?
Post #516358
Posted Thursday, June 12, 2008 8:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 64, Visits: 233
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?
Post #516366
Posted Thursday, June 12, 2008 8:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 64, Visits: 233
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
Post #516367
Posted Thursday, June 12, 2008 9:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 12,910, Visits: 32,013
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #516372
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse