Selecting last 50 entries 2

[alexej]

How can I select the last 50 entries in a table?

 

[calahans]

Do you have a timestamp field ?(I presume by last uoi mean most recent). If you don't have one you will need to add a column using the ALTER TABLE statment.<br><br>SELECT TOP 50 * FROM YOURTABLE WHERE DATE &gt; [SOME RECENT DATE]<br> <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br> Nobody told me that the price of shares could fall... :-(

 

[carp]

What RDBMS are you using and how are you defining &quot;last 50 entries&quot; (that is, based on time of entry, time of update, some value?).<br>

 

[alexej]

Hi, actually I'm logging visitors to my site to a database. I'm using Access now, but I'm going to migrate it to SQL-server. So last 50 entries are based on time of entry. Calahans tip above (SELECT TOP 50) doesn't work in my ASP script. It just ignores this &quot;TOP 50&quot; statement. Could you please help?<br>Thanks a lot!

 

[ssingh]

The Distinct Top&nbsp;&nbsp;will work with the SQL server . Iv'e taken this command from the Book online of SQL server. Please try it with the necessary logic.<br><br>SELECT DISTINCT TOP 10 ShipCity, ShipRegion <br><br>FROM Orders <br><br>ORDER BY ShipCity <br><br><br>Thanx<br>Siddhartha Singh<br><A HREF="mailto:ssingh@aztecsoft.com">ssingh@aztecsoft.com</A><br><br><br><br>More help<br><br>query specification&gt; ::= <br>&nbsp;&nbsp;&nbsp;&nbsp;SELECT [ ALL ¦ DISTINCT ]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[ {TOP integer ¦ TOP integer PERCENT} [ WITH TIES] ] <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;select_list&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;[ INTO new_table ]<br>&nbsp;&nbsp;&nbsp;&nbsp;[ FROM {&lt;table_source&gt;} [,...n] ]<br>&nbsp;&nbsp;&nbsp;&nbsp;[ WHERE &lt;search_condition&gt; ]<br>&nbsp;&nbsp;&nbsp;&nbsp;[ GROUP BY [ALL] group_by_expression [,...n] <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[ WITH { CUBE ¦ ROLLUP } ]<br>&nbsp;&nbsp;&nbsp;&nbsp;]<br>&nbsp;&nbsp;&nbsp;&nbsp;[ HAVING &lt;search_condition&gt; ]<br>

 

[Khaldryck]

on VMS RDBMS, i used something like this :<br><br>SELECT DISTINCT user.user_id FROM users <br>ORDER BY user.date_of_entry DESC<br>LIMIT TO 50 ROWS;<br><br>try it, i don't know if it works on microsoft SQLs<br>since there are lot of things we cannot do with microsoft<br><br>let me know if it worked

 

[alexej]

No :-( Neither DISTINCT TOP 50 nor LIMIT TO 50 ROWS works with MS Access. Thanks anyway

 

[carp]

According to the Help utility in MS ACCESS, you should be able to do something like this:<br><br>SELECT TOP 50 <br>FROM your_table<br>ORDER BY date_of_entry DESC;<br><br>Of course, you need a column to record the timestamp!

 

[EdMacDonald]

If you are accessing the DB from ASP, then MS Jet is probably the data provider.&nbsp;&nbsp;You should make sure your server has the latest drivers because TOP does work with ASP/Jet/Access as posted here.<br><br>Ed

 

[alexej]

Yes, I do access my DB from ASP and I suppose I have the latest verson of MS Jet which should come with Access 2000 I'm using, but it doesn't accept TOP anyway.

 

[EdMacDonald]

Did you actually install Access on your server?<br>Are you running IIS 4 or later?

 

[alexej]

I'm test running it first on my PWS

 

[EdMacDonald]

If you want to post your code, errors, and a lot more detail, we'll have a look.&nbsp;&nbsp;There is something missing from the story that we're not getting here.<br><br>Ed<br><br>

 

[stoleru]

Assuming that your table is log_table and the criteria column for selecting is log_date (which must contain a time component for the query to be consistent), look at this query:<br><FONT FACE=monospace>SELECT * FROM log_table a WHERE 50&gt;<br>(SELECT COUNT(*) FROM log_table b WHERE a.log_date &lt; b.log_table);<FONT FACE=monospace><br> <p>Eduard Stoleru<br><a href=mailto:aeg@ziua.ro>aeg@ziua.ro</a><br><a href= > </a><br>