How to limit the sql to reurn only 1 result

avihaimar · Mar 1, 2007

Hey,

My application give the user the ability to create a virtual view.

The user insert a query - "selcet product_id from product" and we show him the structre of this virtual view.

In order to get the column types i run this query than get the "ResultSetMetaData" ( by getMetaData() ) and finally get the column type from the ResultSetMetaData.

I dont need the full results for the query, so i want to limit the results for 1.
Does any one has an idea?

Thank you

jadams0173 · Mar 1, 2007

Select Top 1 product_id from product

I tried to have patience but it took to long!

-DW

jrbarnett · Mar 1, 2007

Which database engine is your user using? Is it Access, Oracle, SQL Server etc? There are various solutions, but they are engine specific.

John

avihaimar · Mar 1, 2007

I need a generic solution.
currently i solved it by setting stmt.setMaxRows(1), but i read that it dosnt gurrenty better performance .

alvechurchdata · Mar 1, 2007

I need a generic solution.

There is no generic solution.

If you can tell us which database you are using then perhaps somebody will be able to help.

Geoff Franklin

http://www.alvechurchdata.co.uk

jrbarnett · Mar 1, 2007

jadams0173's solution will work in Microsoft databases (Access or SQL server). Probably the nearest to a completely database engine independent solution is something like:

SELECT min (product_id) As [Product ID]
FROM product
WHERE condition

Important: You must set the where clause here to only retrieve data from the rows that you need, or you will get the smallest product ID in the table every time.

John

SQLSister · Mar 7, 2007

And if you have multiple records then what criteria determines which one you show? Ids it the newest one, the oldest one or the one with a specific value in one field? This can dictate waht you add to the query in where clause to get the proper results.

Questions about posting. See faq183-874

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to limit the sql to reurn only 1 result

avihaimar

Programmer

jadams0173

Technical User

jrbarnett

Programmer

avihaimar

Programmer

alvechurchdata

Programmer

jrbarnett

Programmer

SQLSister

Programmer

Similar threads

Part and Inventory Search

Sponsor