CODEWITHSUNDEEP
javasqldatabase
GuruKulki
GuruKulki

Reputation: 26428

Simple question on database query

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.

Upvotes: 2

Views: 295

Answers (5)

Carl Manaster
Carl Manaster

Reputation: 40356

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

Upvotes: 1

Karl
Karl

Reputation: 2955

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

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

Upvotes: 0

Thilo
Thilo

Reputation: 262860

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.

Upvotes: 0

Kyle Rosendo
Kyle Rosendo

Reputation: 25287

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

Upvotes: 5

ozczecho
ozczecho

Reputation: 8869

Select Top 3....

Upvotes: 1

Related Questions