The following questions were taken from various database-related newsgroups on UNIX. Responses are shown as links to each question.
>: Ng K C Paul wrote:
>: > I have a table like this:
>: > dep emp salary >: > --- --- ------ >: > 10 1 100 >: > 10 2 150 >: > 20 1 220 >: > 20 2 330 >: > 30 1 500 >: > 30 2 600>: > I want the result like this:
>: > dep difference >: > --- ---------- >: > 10 50 >: > 20 110 >: > 30 100>: > How can I select the result using SQL?
In response to a question, the user clarified:
> Put it in this way may be simpler. I want to calculate differences of > values of
> row 1 and row 2 > row 3 and row 4 > row 5 and row 6 > row 6 and row 7 > row 8 and row 9 > row 9 and row 10Note: First try to find a solution for the problem as posed the first time around. Then look at the modified version. Does this change the solution? If so, how?
I'm trying to get the total orders per state. The following SQL query works:
1. SELECT order.state_code, SUM(order.value) FROM order GROUP BY order.state_codeBut the following does not:
2. SELECT order.state_code, state.name, SUM(order.value) FROM order, state WHERE order.state_code = state.code GROUP BY order.state_codeThis statement ignores the join and gives a product of the state order values with the rows in the state table. This however does work:
3. SELECT order.state_code, (SELECT name FROM state WHERE code = order.state_code), SUM(order.value) FROM order GROUP BY order.state_codeWhat is wrong with #2?
How to make a number for all records in SELECT:
SELECT name , nn FROM table1 WHERE ... John 1 Monna 2 Sam 3 Tom 4(Note: No numbers stored in table1.)
Hello all:
I have two questions about SQL using Sybase.
1. There is a timestamp field defined as datetime in a table. Every time a row was inserted to the table would be timestamped. How can I count the number of rows in the group of 5 seconds in a minute?
2. Is it possible to return only first 10 rows from a result set which contains more than 10 rows from a query? The situation arises in the application supports multiple query forms such as name, age, address against a huge database. If the user simply put in a char 'A' in the name input field, the query will generate a tremendous result set with name beginning with 'A' regardless the age or address. Hopefully, there is a way to stop the execution of a query after returning the first some portion of result.
Thanks in advance.
I have a "hotel" system that has "rooms" and "reservations". There are multiple reservations for a room by date. I am simply trying to join the rooms to the reservations but *only* return *one* row per room with the most recent reservation (the one that starts closest to todays date).
However, my data model is a little more sophisticated. The reservation table has a primary key called reservation_id and it also has a foreign key guest_id plus other attributes of the reservation. Here is a better example:
Guest Table: Guest_id, guest_name, guest_phone 01, Bill Smith, 555-5555 02, Bob Jones, 444-4444 Rooms Table: Room_id, no_beds, smoking 01, 2 beds, yes 02, 1 king, no Reservations Table: Reservation_id, room_id, guest_id, arrival_date, departure_date, no_guests 01, 01, 01, 11/20/1998, 11/25/1998, 2 01, 01, 02, 11/27/1998, 12/12/1998, 1Note that there are two reservations for the same room, but on different arrival dates. Im trying to develop a report that lists each room and ONLY the most recent reservation and guest data. For example a result set like this:
Room_id, guest_name, arrival_date, departure date 01, Bill Smith, 11/20/1998, 11/25/1998 (only one row per room, with nearest reservation data)Can anyone help? It would be very appreciated.
Comment: >: We need more information. Are you trying to select the difference >: between two specific employees in each department, or between the >: highest and lowest salary in each department? Will some departments >: will have more or less than 2 employees? >: and later response: I thought so, when I saw your previous post.
The problem is: SQL has no concept of row numbers! There is not inherent ordering of the table. True, there is on the physical level, but if you want to work on a relational database you should only in extreme circumstances refer to the physical level.
As for your problem: I don't think it can be solved in any reasonable way without explicitly adding rownumber to the table.
Proposed solutions:
1 select distinct a.dep, abs( a.sal-b.sal ) difference 2 from foo a, foo b 3 where a.dep = b.dep 4* and a.rowid <> b.rowid SQL> / DEP DIFFERENCE ---------- ---------- 10 50 20 110 30 100Another user suggested:
select dep, difference=max(salary)-min(salary) from table group by depor
select emp1.dep, difference=emp2.salary-emp1.salary from table emp1, table emp2 where emp1.dep=emp2.dep and emp1.emp = 1 and emp2.emp = 2Return to example 1
Comment and possible solution for example 2
Your "group by" clause needs to contain state.name as well, or you will get each grouping of state code & total order value repeated for every value of state name. Try:
SELECT order.state_code, state.name, SUM(order.value) FROM order, state WHERE order.state_code = state.code GROUP BY order.state_code, state.nameReturn to example 2
Possible solutions for example 3
Assuming name is unique !!!
select a.name, Count (b.name) from table1 a, table2 b where a.name >= b.name order by a.nameIf name is not unique, it gets stickier. Assume there is a unique column, or a set of unique columns which can be represented as "tabKey". Then, this will work:
select a.name, Count (b.name) from table1 a, table2 b where a.name >= b.name and a.tabKey > b.tabkey order by a.nameUltimately, depending n table size and optimisation, it may be easier to do the following:
select name, nn = Identity (5) into #tmp from table1 order by name select * from #tmpReturn to example 3
Possible solution for example 4
For part 1: From this I understand you want groups of 5 seconds, within a minute, so you would have a given minute, say 10:15 and then you want 12 records displayed for each of the 5 second intervals in that minute with the number of datetimes in the table which fall in that interval. I have used one of our existing tables as a workplace ... the modification time is stored in a column "update_tm".
declare @minute datetime select @minute = "Nov 26 1998 10:15:00:00AM" Select @minute "minute", 5 * (1 + Floor ( Convert (float, DateDiff (second, @minute, update_tm)) / 12.0)) "interval", Count (*) "Records" from pick_your_table where update_tm between @minute and DateAdd (minute, 1, @minute) group by 5 * (1 + Floor ( Convert (float, DateDiff (second, @minute, update_tm)) / 12.0))This gives me the following output:
minute interval Records Nov 26 1998 10:15:00:000AM 5 90 Nov 26 1998 10:15:00:000AM 10 17 Nov 26 1998 10:15:00:000AM 15 21 Nov 26 1998 10:15:00:000AM 20 72 Nov 26 1998 10:15:00:000AM 25 110The number records updated in the 5 seconds *up to* the interval are recorded in the Records column. The interval is the number of seconds *after* the time in the minute column, which actually does not need to be an exact minute.
It would be substantially more difficult to add the intervals where no records are changed ;-)
For part 2: The rowcount setting is what you want here:
set rowcount 10Remember to set it back to 0 after you have used it otherwise you may be surprised by your results. Also, it is not possible to make the number a variable, so this may constrain you. Many front-end application programming tools (Powerbuilder, etc) allow you to cancel the retrieval of a resultset at a given point as well.
Possible solutions for example 5
(Responses of two users intermixed, followed by response from third user)
(First user's second response, reacting to second user's suggestion)
Oh, heavens. What I meant was:
SELECT Max(Reservations.ArrivalDate) AS MaxOfArrivalDate, Reservations.RoomID FROM Reservations GROUP BY Reservations.RoomID;Which will return the latest reservation for each room. Then link that with the other tables in a normal select query to get the fields he wants.
(First user's initialresponse):
Try grouping by the room, then taking the max() of the date. You may have
to use an intermediate query, or maybe I'm just tired
(Second user's response to first user)
I think you're right about the intermediate query as Dan requires the GuestID - so he'll need some
way of getting the guest that's made the latest reservation.
Dan, if you create a query (I'll call it "future_arrivals") that uses the
reservations table and returns two fields - the room_id and the arrival_date.
Assuming were doing this via the query grid rather than typing in the SQL,
you need to put a criteria value agains the arrival_date column -
">=Date()" (without the quotes). This will return all room_id values that
have a reservation from today onwards and the appropriate arrival_dates.
If you want to ignore today's then change >= to >.
Then create a second query (say "next_arrivals") that uses "future_arrivals" as its source of data. Pass both fields as output, but this time group on "room_id" and return the minimum "arrival_date" for each group.
The final query uses "next_arrivals" as its source and links it to the
reservations table by both room_id and arrival_date. Link the reservations
table to the guests table using the guest_id and you've got all the fields
on the appropriate records to output your report.
(Third user, in response to first and second suggestions)
What's with the Heinz approach? You don't need 57 queries just one.
Page maintained by
evelyn@udel.edu
SELECT a.resv, a.room, c.name, a.arrival, a.depart
FROM tblresv AS a INNER JOIN tblguest AS c
ON a.guest=c.guest
WHERE datediff("d",a.arrival,now)=
(SELECT Min(datediff("d",b.arrival,now)) FROM tblresv AS b
WHERE b.room=a.room AND b.arrival<=now)
ORDER BY a.resv, a.room;
I'm assuming you want the closest date to a given date (now) but not
greater. If not the Min expression can easily be modified. Also assuming
resv (reservation_id) is unique as you state it is primary key.
Last modified on Dec 17, 1998