March 27, 2007 at 11:00 am
Is there a SQL equivalent to MS Access' "first" function? Any help would be GREATLY appreciated. Thank you.
Chris
March 27, 2007 at 12:38 pm
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
March 27, 2007 at 5:08 pm
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
March 28, 2007 at 7:44 am
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
March 28, 2007 at 8:13 am
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
June 12, 2008 at 7:27 pm
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.
June 12, 2008 at 7:35 pm
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?
June 12, 2008 at 8:20 pm
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?
June 12, 2008 at 8:28 pm
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
June 12, 2008 at 9:00 pm
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
June 12, 2008 at 9:32 pm
Stephen Morley (6/12/2008)
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
It talks about DAO, but that's the quote directly out of the FIRST() description. I do understand what you're doing. The recordset object is the default mechanism for accessing table data, whether you do it through code, or use the UI tools.
While I do agree that MOST times, Access will acknowledge the "physical order", I've seen it many times pull any darned thing it wants.
----------------------------------------------------------------------------------
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?
June 12, 2008 at 9:42 pm
Lowell (6/12/2008)
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.
All due respect - but the order is not always respected when pulling FIRST(). You're right - it is quite a bit like a SQL heap, in that in a lot of the circumstances, it will pull data based on the default order (which is essentially the physical order).
The minute you introduce groupings, WHERE clauses, etc...i.e. very common items when dealing with aggregate functions, then any assurance you might have had you would get the "first" row in physical order gets chucked right out the window.
The part about ORDER BY being optional was to show that you could even replicate the somewhat random selection you might get out of Access' FIRST() aggregate function.
One of the wonderful things about FIRST was that it was a much lower effort aggregate that could pull a "sample" from one row in a group and display it, especially when it didin't really matter which one it pulled.
----------------------------------------------------------------------------------
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?
June 12, 2008 at 9:49 pm
Stephen Morley (6/12/2008)
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?
Interesting because having worked with it just as long -I've seen exactly the opposite.
Don't take my word for it - take a look at the help. That's where I'm pull that info from, and it's behavior I've seen.
----------------------------------------------------------------------------------
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?
June 12, 2008 at 11:30 pm
Lowell has hit the nail on the head!
If you repair and compact the First() function it now returns A rather than B.
However this is only if the table has a primary key. If no key is defined the order is retained and First() still returns B.
This would explain why Matt has seen inconsistencies whereas I have not. The tables I have seen it used do not have a primary key whereas the tables Matt has seen must have had a primary key or an index.
Seems like TOP 1 ORDER BY is the only real solution. If you are using First() on a table in MS Access with a primary key the results are going to be inconsistent between compacts but at least with SQL server you will get the same result every time. If you are using First() on a table in MS Access without a primary key the results are going to be consistent and so will the results of TOP 1 ORDER BY.
Regards
Steve
June 13, 2008 at 6:46 am
Yup - I certainly use primary keys on my Access tables, which I think also throws in some extra "incertainties" into these. The behavior around compact does sound familiar, but I could swear it happened more than that (it's been a while since I tried to analyze that kind of behavior - I've been taken it for granted for a few years).
Anyway - It's good we managed to sort it out. And back to the original question - sounds like Top x is the consensus to replace FIRST. You have other options in 2005 , but I think you're "stuck with" Top X for now.....
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply