Im using an odbc-jdbc bridge in my project and I need select 2 pieces of data from the database and save the data to 2 variables on the java side of my application. Here is an example of my table.
SITE_ID ------- DEV_ID ------- SCHEDULE_TIME ------- VALUE_ENUM ------- IDX
---------------------------------------------------------------------------
1 3000 09:30:00 1 1
1 3000 11:30:00 0 2
1 3000 12:00:00 1 3
1 3000 14:00:00 0 4
1 3000 18:30:00 1 5
1 3000 20:30:00 0 6
1 4000 05:00:00 1 1
1 4000 13:30:00 0 2
1 4000 16:30:00 1 3
1 4000 18:30:00 0 4
What I want to do is select SCHEDULE_TIME for the last 2 IDX's where DEV_ID is 3000, so I would like to save 18:30:00 and 20:30:00 in a variables, some examples of statements Ive tried are:
select SCHEDULE_TIME from ARRAY_BAC_SCH_Schedule order by IDX desc limit 1 where DEV_ID = 3000
select SCHEDULE_TIME from ARRAY_BAC_SCH_Schedule order by IDX desc limit (1,1) where DEV_ID = 3000
SELECT TOP 1 SCHEDULE_TIME FROM ARRAY_BAC_SCH_Schedule WHERE DEV_ID = 3000 ORDER BY IDX DESC
Right now Im just worrying about how to get the select statement to work in Query tool before I implement it in the java side. Thanks, Beef.
For SQL Server you should use
SELECT TOP 2 SCHEDULE_TIME from (select SCHEDULE_TIME FROM ARRAY_BAC_SCH_Schedule WHERE DEV_ID = 3000 ORDER BY IDX DESC) as inner
like Hemal told you.
Be careful with queries like
select TOP 2 SCHEDULE_TIME FROM ARRAY_BAC_SCH_Schedule WHERE DEV_ID = 3000 ORDER BY IDX DESC
because that is wrong. SQL Server does the top and then the order.
In PostgreSQL or MySQL you should use limit and the end of the query. The limit is after the where part.
In Oracle you should use rownum inside the where part.