How to limit the sql to reurn only 1 result

[avihaimar]

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]

Select Top 1 product_id from product

I tried to have patience but it took to long! -DW

 

[jrbarnett]

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]

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

 

[alvechurchdata]

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]

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]

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