Same SQL query on each array element

Question

I am writing a query for my database and basically I want to query a database for events happening in each room. Meaning that i want to find the earliest event happening in room 1 and the earliest event happening in room 2.

Below I have the code for performing this query for 1 room. This code only returns the earliest event in room 2. How can I perform a query where I find the earliest event for room 1 and the earliest event for room 2 and return it in a single query?

This would be performing a query on each element of an array but in a single query. Is this possible or would I have to write a query for each room?

select   name,
         eventtime,
         MIN(eventtime - currenttime) as time_from_test
from     the_table
where    the_table.room = Room1
group by the_table.room
order by time_from_test
limit 1

Thanks in advance

ur group by columns r not matching with the select clause... — Teja
– Teja, Commented Mar 16, 2012 at 19:34

Teja · Accepted Answer · 2012-03-16 20:43:20Z

3

select   the_table.room,MIN(eventtime) as event_start_time_of_day
from     the_table
group by the_table.room;

For eg. if you have 5 rooms and each room has five event times then this query will retrieve the minimum event time and return it back to you by grouping all events at room level.

edited Mar 16, 2012 at 20:43

answered Mar 16, 2012 at 19:36

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dave Over a year ago

To get more information (like the_table.name) you could join back to the_table again, I think.

Teja Over a year ago

Yup.. for getting other information I can join with the same table on room and get it...

maknelly Over a year ago

Hi Venk, what if I am getting the rooms from another table? How would I find earliest events in the current table from the rooms that I select from the other table? Basically, I find the rooms in the other table and use the current table as a lookup table for events

Collectives™ on Stack Overflow

Same SQL query on each array element

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related