Jump to content

SELECT TOP 4 ONLY - HELP


Rob Waite

Recommended Posts

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

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

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

  • 2 weeks later...
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

Hi, u try like thisselect top 4 * from tablename orderby desc
Link to comment
Share on other sites

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

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 there
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.

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...