Rob Waite Posted November 24, 2005 Share Posted November 24, 2005 Hello,I have written a statement to get the TOP 4 from a Particular table, based on a Number Field (ORDER BY DESC)My problem is that if this number field that i'm using to order by has, for example the same value 7 times then i get a return of 7 rows.eg.Letter NumberA 10B 10C 10D 9E 9F 8I want the an SQL statement to get the TOP 4 from this, but as you can see D & E have the same values, so i returns 5 rows.I am using an ACCESS database, any help would be appreciated.Rob Link to comment Share on other sites More sharing options...
Jerome Posted November 24, 2005 Share Posted November 24, 2005 I don't get it...Wich sql statement are you using? Something like this : SELECT TOP 4 * FROM yourDataBase Link to comment Share on other sites More sharing options...
Rob Waite Posted November 24, 2005 Author Share Posted November 24, 2005 Yeah, thats right - with an ORDER BY on the end Link to comment Share on other sites More sharing options...
Rob Waite Posted November 24, 2005 Author Share Posted November 24, 2005 I only want to display the top 4 only (i know the post subject says 6, but i meant 4) Link to comment Share on other sites More sharing options...
Jerome Posted November 24, 2005 Share Posted November 24, 2005 Ok Rob, I don't see what's wrong... I created a table like yours and used the query I showed you and there is no problem... Paste your code and the structure of your table (name, fields,...) Link to comment Share on other sites More sharing options...
vijay Posted November 26, 2005 Share Posted November 26, 2005 Hi, U know How much records U wantSo Select records Ordered by asc/desc acrding to ur requirementand select only that much row onlyThis is u want suppose if i m not wrongOkbest luck Link to comment Share on other sites More sharing options...
Pradyumna Posted December 5, 2005 Share Posted December 5, 2005 Hello,I have written a statement to get the TOP 4 from a Particular table, based on a Number Field (ORDER BY DESC)My problem is that if this number field that i'm using to order by has, for example the same value 7 times then i get a return of 7 rows.eg.Letter   NumberA       10B       10C       10D       9E       9F        8I want the an SQL statement to get the TOP 4 from this, but as you can see D & E have the same values, so i returns 5 rows.I am using an ACCESS database, any help would be appreciated.Rob<{POST_SNAPBACK}> Hi, u try like thisselect top 4 * from tablename orderby desc Link to comment Share on other sites More sharing options...
Skemcin Posted December 5, 2005 Share Posted December 5, 2005 If you want the TOP 4 records of from a table and the top 10 are tied for "first place", then you will need to add another field into your ORDER BY clause. On every database I ever made, I always included an automatically populated field for date_added. In your case, I would have my statement read like this: SELECT TOP 4 *FROM tablenameORDER BY number DESC, date_added DESC This way only the latest 4 of the 10 tied for will be outputed. You could interchange your primary key - if its an autonumber - with date_added. Link to comment Share on other sites More sharing options...
_underscore_ Posted December 5, 2005 Share Posted December 5, 2005 SELECT TOP 4 *FROM tablenameLIMIT 4 Shouldn't that work? Link to comment Share on other sites More sharing options...
aspnetguy Posted December 5, 2005 Share Posted December 5, 2005 providing you are using a MySql data base minus the (TOP 4).The Top # is only for SQL Server and in oracle you have to use something like this WHERE ROWNUM <= 4 Link to comment Share on other sites More sharing options...
Skemcin Posted December 5, 2005 Share Posted December 5, 2005 SELECT TOP 4 *FROM tablenameLIMIT 4 Shouldn't that work? Yes, but from what I gather the problem is when there are more than 4 entries with the same value. How do you determine which are the TOP 4? I've run into this issue when trying to display the ten questions (out of 50) that some ranked. Well if they ranked 15 a 1, then which of the 15 get listed when a TOP 5 (or LIMIT 5 - for oracle) gets coded. Thats why I suggested using a second field to order by - to add the chronological order to the sort. Its hard to catch in his original post, but this requirement is thereMy problem is that if this number field that i'm using to order by has, for example the same value 7 times then i get a return of 7 rows. Link to comment Share on other sites More sharing options...
aspnetguy Posted December 5, 2005 Share Posted December 5, 2005 PLEASE....TOP 4 is for SQL Server, LIMIT 4 is for MySql...you cannot use both! Link to comment Share on other sites More sharing options...
Kcarson Posted December 5, 2005 Share Posted December 5, 2005 PLEASE....TOP 4 is for SQL Server, LIMIT 4 is for MySql...you cannot use both! <{POST_SNAPBACK}> By the way just to clarify, he is using Access, not SQL Server, Oracle, or MySQL. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now