Simple question on database query.

Question

I have been asked in an interview to write a SQL query which fetches the first three records with highest value on some column from a table. I had written a query which fetched all the records with highest value, but didn't get how exactly i can get only first three records of those.

Could you help me in this.

Thanks.

Answer 1

SELECT TOP 3 * FROM Table ORDER BY FieldName DESC

From here, but might be a little out of date:

Postgresql:

SELECT * FROM Table ORDER BY FieldName DESC LIMIT 3

MS SQL Server:

SELECT TOP 3 * FROM Table ORDER BY FieldName DESC

mySQL:

SELECT * FROM Table ORDER BY FieldName DESC LIMIT 3

Answer 2

Depending on the database engine, either

select top 3 * from table order by column desc

or

select * from table order by column desc limit 3

Answer 3

Select Top 3....

Answer 4

The syntax for TOP 3 varies widely from database to database.

Unfortunately, you need to use those constructs for the best performance. Libraries like Hibernate help here, because they can translate a common API into the various SQL dialects.

Since you are asking about Java, it is possible to just SELECT everything from the database (with an ORDER BY), but just fetch only the first three rows. Depending on how the query needs to be executed this might be good enough (especially if no sorting on the database has to happen thanks to appropriate indexes, for example when you sort by primary key fields).

But in general, you want to go with an SQL solution.

Answer 5

In oracle you can also use where rownum < 4...

Also on mysql there is a Limit keyword (i think)