select data from last 2 rows sql

Question

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.

get the select statement to work in Query tool? what do you mean ? — Wadih M., Sep 22 '11 at 17:26
@Wadih M.: Likely referring to the query tool provided with the database. Beef wants to test the query before implementing it in Java. — Derek Springer, Sep 22 '11 at 17:29
As Terrell said you will need to check what kind of database are you using because the SQL to run will be different depending on it. Other option (with worst performance) could be to select all results ordering using the `IDX` and iterate to retrieve 2 rows (as a maximum). — Nacho Cougil, Sep 22 '11 at 17:31
its through Delta Controls so Im not entirely sure which kind of database it is, Im pretty sure its just a SQL DB cause all the other lines Ive used for other tasks work fine, and any other DB I have had experience with has always been sql — Beef, Sep 22 '11 at 17:36

score 1 · Answer 1 · answered Sep 22 '11 at 17:28

1

Maybe I'm missing something. Is there a reason you wouldn't just run:

select SCHEDULE_TIME from ARRAY_BAC_SCH_Schedule order by IDX desc limit 2 where DEV_ID = 3000

This should return 2 rows, containing 20:30:00 and 18:30:00.

answered Sep 22 '11 at 17:28

Elliot Nelson

11,371
3
30
44

when i run that line I get error saying Unexpected extra token: limit – Beef Sep 22 '11 at 17:31

score 1 · Answer 2 · answered Sep 22 '11 at 17:28

1

if it is mysql then

 select SCHEDULE_TIME from ARRAY_BAC_SCH_Schedule where DEV_ID = 3000 order by IDX desc limit 2

answered Sep 22 '11 at 17:28

Ujjwal Manandhar

2,194
16
20

score 1 · Answer 3 · answered Sep 22 '11 at 17:29

1

I think

SELECT TOP 2 * from (select SCHEDULE_TIME FROM ARRAY_BAC_SCH_Schedule WHERE DEV_ID = 3000 ORDER BY IDX DESC) as inner

answered Sep 22 '11 at 17:29

Miserable Variable

28,432
15
72
133

With this one I get an error saying: Expected lexical element not found: FROM – Beef Sep 22 '11 at 17:35

Derek Springer · Answer 4 · 2011-09-22T17:42:42.817

1

The specifics will vary a little based on your DB, but your query should look something like this:

SELECT TOP 2 SCHEDULE_TIME 
FROM ARRAY_BAC_SCH_Schedule
WHERE DEV_ID = 3000
ORDER BY IDX Desc

Or, on one line:

SELECT TOP 2 SCHEDULE_TIME FROM ARRAY_BAC_SCH_Schedule WHERE DEV_ID = 3000 ORDER BY IDX Desc

edited Sep 22 '11 at 17:42

answered Sep 22 '11 at 17:33

Derek Springer

2,666
1
14
12

With this one I get an error saying: Expected lexical element not found: FROM – Beef Sep 22 '11 at 17:38

score 1 · Accepted Answer · answered Sep 22 '11 at 17:40

1

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.

answered Sep 22 '11 at 17:40

DavidEG

5,857
3
29
44

with the top SQL Server statement I get an error: `Expected lexical element not found: FROM State: 37000; Native: 1015; Origin: [Delta Controls Inc.][Delta Controls ODBC Driver]` – Beef Sep 22 '11 at 17:44
"inner" is not a good name... try this: SELECT TOP 2 [SCHEDULE_TIME] from (select [SCHEDULE_TIME] FROM [ARRAY_BAC_SCH_Schedule] WHERE [DEV_ID] = 3000 ORDER BY [IDX] DESC) as [schTimesFiltered] – DavidEG Sep 22 '11 at 17:54
I still get the same error, Im not that new to sql I can usually figure out queries on my own, but since Ive been working with this database I run into a lot of errors I dont recognize – Beef Sep 22 '11 at 17:57
But, is it a SQL Server? Which version? – DavidEG Sep 22 '11 at 18:03
Im not exactly sure, is there a way to find out? or just ask someone that knows – Beef Sep 22 '11 at 18:04
If it is a SQL Server with `select @@version` you will get the version. If it is not you will get an error :) – DavidEG Sep 22 '11 at 18:06
I usually do all my sql work with sql server 2008 and management studio, but I could not get management studio to connect to a machine data source so instead I use Query Tool(using odbc) V6.1 to do all my queries, and I got an error with the select @@version – Beef Sep 22 '11 at 18:06
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/3702/discussion-between-davideg-and-beef) – DavidEG Sep 22 '11 at 18:07

Julio Cesar Rodriguez · Answer 6 · 2011-09-22T17:49:15.633

1

You can do that with subquerys:

select * from array_bac_sch_schedule where (value_enum,idx) in (select value_enum,idx from array_bac_sch_schedule where dev_id=3000) order by schedule_time desc limit 2;

I took value_enum and idx as primary key.

edited Sep 22 '11 at 17:49

answered Sep 22 '11 at 17:47

Julio Cesar Rodriguez

11
2

select data from last 2 rows sql

6 Answers6