[SQL] Need to overcome UNION / ORDER BY restriction
I have a table: id | priority | seniority -+--+--- 902 |2 | 271 902 |1 | 271 902 |3 | 271 924 |2 | 581 924 |1 | 581 924 |3 | 581 935 |1 | 276 935 |2 | 276 972 |2 | 275 (9 rows) I'd need to get a result set where rows are sorted according to these rules: 1. first all rows with priority = 1 sorted according to seniority 2. then the rest of the rows sorted by seniority, priority Something like this: SELECT * from foo where priority = 1 order by seniority union select * from foo where priority > 1 order by seniority, priority but this gives parse error because of the restrictions with ORDER BY and UNION (I suppose..) select * from foo order by case when priority = 1 then priority else seniority end; goes fine, but it's not quite enough and when I try select * from foo order by case when priority = 1 then priority else seniority, priority end; it's parse error at or near ",". Any suggestions? Thanks in advance, Timo --- CREATE TABLE foo ( id integer, priority integer, seniority integer ); INSERT INTO foo VALUES (902, 2, 271); INSERT INTO foo VALUES (902, 1, 271); INSERT INTO foo VALUES (902, 3, 271); INSERT INTO foo VALUES (924, 2, 581); INSERT INTO foo VALUES (924, 1, 581); INSERT INTO foo VALUES (924, 3, 581); INSERT INTO foo VALUES (935, 1, 276); INSERT INTO foo VALUES (935, 2, 276); INSERT INTO foo VALUES (972, 2, 275); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] A tricky sql-query...
We have a small association and the association has a cabin. Members of the association can rent a term to stay in the cabin but as the cabin has turned out to be very famous we have had to establish an application policy for that. It goes like this: 1. There's a seniority queue for this purpose (once you've got a term you'll be placed in the last position in the queue) 2. Members can apply for one or more of the terms 3. The top one member in this seniority queue gets the term he applies. 4. The second member in the queue gets the term he primarly applies unless it's not being taken by the first member. If this is the case then take his secondary quest. 5. The third member gets the term he's primarly applied unless it's not being taken by the first or the second applicant. If it is then try his secondary application. If that's taken as well then try his 3rd quest (if he has such) 6. and so on.. So, (if you didn't understand anything it's OK, pardon my poor English) if I have a table for the applies: CREATE TABLE apply_demo ( memberid integer, sen integer, priority integer, termid integer ); INSERT INTO apply_demo VALUES (2041, 115, 1, 15); INSERT INTO apply_demo VALUES (2041, 115, 2, 18); INSERT INTO apply_demo VALUES (2041, 115, 3, 19); INSERT INTO apply_demo VALUES (206, 120, 1, 13); INSERT INTO apply_demo VALUES (6571, 184, 1, 16); INSERT INTO apply_demo VALUES (123340, 213, 1, 4); INSERT INTO apply_demo VALUES (123340, 213, 2, 16); INSERT INTO apply_demo VALUES (123340, 213, 3, 9); INSERT INTO apply_demo VALUES (152946, 301, 1, 5); INSERT INTO apply_demo VALUES (152880, 302, 1, 13); INSERT INTO apply_demo VALUES (152880, 302, 2, 14); INSERT INTO apply_demo VALUES (181333, 332, 1, 17); INSERT INTO apply_demo VALUES (242502, 462, 1, 9); INSERT INTO apply_demo VALUES (246024, 473, 1, 18); INSERT INTO apply_demo VALUES (246024, 473, 2, 19); INSERT INTO apply_demo VALUES (246024, 473, 3, 13); INSERT INTO apply_demo VALUES (245954, 475, 1, 11); INSERT INTO apply_demo VALUES (245954, 475, 2, 12); INSERT INTO apply_demo VALUES (245954, 475, 3, 16); INSERT INTO apply_demo VALUES (245954, 475, 4, 8); INSERT INTO apply_demo VALUES (152972, 510, 1, 13); INSERT INTO apply_demo VALUES (152972, 510, 2, 4); INSERT INTO apply_demo VALUES (152972, 510, 3, 16); INSERT INTO apply_demo VALUES (152972, 510, 4, 22); INSERT INTO apply_demo VALUES (152972, 510, 5, 2); INSERT INTO apply_demo VALUES (254085, 537, 1, 8); INSERT INTO apply_demo VALUES (288842, 640, 1, 8); I'd need to get out something like this: termid | gotby + 2 | 3 | 4 | 123340 5 | 152946 6 | 7 | 8 | 254085 9 | 242502 10 | 11 | 245954 12 | 13 |206 14 | 152880 15 | 2041 16 | 6571 17 | 181333 18 | 246024 19 | 20 | 21 | 22 | 152972 (21 rows) I know you Gurus are busy and as you are, don't spend too much time on this because it has already been implemented with PL/PgSQL. But just out of the curiosity - and for the educational purposes :) - I'd like to know whether you can do this with a single sql-query? You can't have any recursion in an pure sql-query, can you? Regards, Timo ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] datatype conversion on postgresql 7.4.1
hi everybody, i got the following problem: i have a database running on a postgresqlserver version 7.4.1, in the database is a table with 4 columns containing float-numbers. what i want to do now is, to convert the type of those columns from float to numeric, because i want to limit the positions after the decimal point to 2. what works fine in v. 8: either alter table fragment alter column x type numeric(15,2) or update fragment set x = round(x,2) but both wont work with version 7.4.1! when i look into the documentation it seems to say that in 7.4.1 round() can only round to a given number of positions after the decimal point when used with a numeric type, but not with floats! and if i try something like: alter table fragment alter column x type numeric(15,2) i get an syntax error, so this seems to be no feature in 7.4.1 (didnt find anything like that in the docs too) what can i do to solve this? there must be some way in postgresql 7.4.1, if there is no way to do that, i have to build up the whole database from scratchthere must be a way to convert float to numeric values in 7.4.1 , but i dont have a single clue... thx for any help in advance ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Please help with a query..
I'm stucked.. Say a car travels from X to Y then from Y to Z (and then from Z back to X but that's not relevant here). In the table below are the timestamps for each point in various dates. The complete trip X-Y-Z-X is in the table but each leg on a separate row. I'd need to get the time interval X-Y-Z on each date but I cannot rely on the date (can't make any joins based on the date part of timestamps). Instead I'd need to find out X-Y and Y-Z pairs with a minimal "stop" -time at Y and calculate total travel time for those. The output would be something like this: dep_dateX_Y_Z_time 2005-01-11 6 hours 15 mins 2005-01-12 5 hours 49 mins 2005-01-13 6 hours 05 mins (the times above are not correct) Any help would be greatly appreciated. Thanks, Timo CREATE temp TABLE foo ( pta text, atime timestamp without time zone, ptb text, btime timestamp without time zone ); COPY foo (pta, atime, ptb, btime) FROM stdin using delimiters ','; X,2005-01-11 06:06:00,Y,2005-01-11 08:00:00 X,2005-01-12 06:10:00,Y,2005-01-12 08:00:00 X,2005-01-13 06:14:00,Y,2005-01-13 08:20:00 X,2005-01-14 06:32:00,Y,2005-01-14 08:17:00 X,2005-01-17 06:14:00,Y,2005-01-17 08:02:00 X,2005-01-18 06:10:00,Y,2005-01-18 07:57:00 X,2005-01-19 06:14:00,Y,2005-01-19 08:06:00 X,2005-01-20 06:26:00,Y,2005-01-20 08:13:00 X,2005-01-21 07:52:00,Y,2005-01-21 09:45:00 X,2005-01-24 06:09:00,Y,2005-01-24 07:56:00 X,2005-01-25 06:18:00,Y,2005-01-25 08:07:00 X,2005-01-26 06:05:00,Y,2005-01-26 07:45:00 X,2005-01-27 06:16:00,Y,2005-01-27 07:54:00 X,2005-01-28 06:18:00,Y,2005-01-28 07:59:00 X,2005-01-31 06:50:00,Y,2005-01-31 08:44:00 X,2005-02-01 06:15:00,Y,2005-02-01 07:55:00 X,2005-02-02 06:12:00,Y,2005-02-02 07:59:00 X,2005-02-03 06:31:00,Y,2005-02-03 08:03:00 X,2005-02-04 06:08:00,Y,2005-02-04 07:53:00 X,2005-02-07 06:18:00,Y,2005-02-07 08:09:00 X,2005-02-08 06:02:00,Y,2005-02-08 07:49:00 X,2005-02-09 06:16:00,Y,2005-02-09 08:02:00 X,2005-02-10 06:12:00,Y,2005-02-10 08:07:00 X,2005-02-11 06:13:00,Y,2005-02-11 08:04:00 X,2005-02-14 06:20:00,Y,2005-02-14 08:11:00 X,2005-02-15 06:20:00,Y,2005-02-15 08:06:00 X,2005-02-16 06:11:00,Y,2005-02-16 08:01:00 X,2005-02-17 06:14:00,Y,2005-02-17 07:59:00 X,2005-02-18 06:13:00,Y,2005-02-18 07:59:00 X,2005-02-21 06:15:00,Y,2005-02-21 08:14:00 X,2005-02-22 06:23:00,Y,2005-02-22 08:10:00 Z,2005-01-11 10:15:00,X,2005-01-11 11:58:00 Z,2005-01-12 10:09:00,X,2005-01-12 11:47:00 Z,2005-01-13 10:18:00,X,2005-01-13 12:06:00 Z,2005-01-14 10:15:00,X,2005-01-14 12:06:00 Z,2005-01-17 10:25:00,X,2005-01-17 12:13:00 Z,2005-01-18 10:16:00,X,2005-01-18 11:55:00 Z,2005-01-19 10:15:00,X,2005-01-19 12:00:00 Z,2005-01-20 10:27:00,X,2005-01-20 12:17:00 Z,2005-01-21 11:28:00,X,2005-01-21 13:25:00 Z,2005-01-24 10:17:00,X,2005-01-24 12:05:00 Z,2005-01-25 10:20:00,X,2005-01-25 12:16:00 Z,2005-01-26 10:17:00,X,2005-01-26 12:21:00 Z,2005-01-27 10:30:00,X,2005-01-27 12:38:00 Z,2005-01-28 10:24:00,X,2005-01-28 12:19:00 Z,2005-01-31 10:30:00,X,2005-01-31 12:18:00 Z,2005-02-01 10:19:00,X,2005-02-01 12:22:00 Z,2005-02-02 10:17:00,X,2005-02-02 12:17:00 Z,2005-02-03 10:14:00,X,2005-02-03 12:04:00 Z,2005-02-04 10:18:00,X,2005-02-04 12:16:00 Z,2005-02-07 10:10:00,X,2005-02-07 12:02:00 Z,2005-02-08 10:10:00,X,2005-02-08 11:57:00 Z,2005-02-09 10:18:00,X,2005-02-09 12:06:00 Z,2005-02-10 10:19:00,X,2005-02-10 12:04:00 Z,2005-02-11 10:14:00,X,2005-02-11 11:58:00 Z,2005-02-14 11:11:00,X,2005-02-14 13:04:00 Z,2005-02-15 10:20:00,X,2005-02-15 12:13:00 Z,2005-02-16 10:34:00,X,2005-02-16 12:22:00 Z,2005-02-17 10:20:00,X,2005-02-17 12:09:00 Z,2005-02-18 10:23:00,X,2005-02-18 12:08:00 Z,2005-02-21 10:30:00,X,2005-02-21 12:24:00 Z,2005-02-22 10:19:00,X,2005-02-22 12:13:00 Y,2005-01-11 08:46:00,Z,2005-01-11 09:33:00 Y,2005-01-12 08:40:00,Z,2005-01-12 09:25:00 Y,2005-01-13 08:56:00,Z,2005-01-13 09:45:00 Y,2005-01-14 08:55:00,Z,2005-01-14 09:44:00 Y,2005-01-17 08:48:00,Z,2005-01-17 09:34:00 Y,2005-01-18 08:54:00,Z,2005-01-18 09:43:00 Y,2005-01-19 08:48:00,Z,2005-01-19 09:32:00 Y,2005-01-20 08:58:00,Z,2005-01-20 09:51:00 Y,2005-01-21 10:19:00,Z,2005-01-21 11:08:00 Y,2005-01-24 08:48:00,Z,2005-01-24 09:45:00 Y,2005-01-25 08:50:00,Z,2005-01-25 09:43:00 Y,2005-01-26 08:44:00,Z,2005-01-26 09:26:00 Y,2005-01-27 08:52:00,Z,2005-01-27 09:39:00 Y,2005-01-28 08:46:00,Z,2005-01-28 09:37:00 Y,2005-01-31 09:21:00,Z,2005-01-31 10:05:00 Y,2005-02-01 08:49:00,Z,2005-02-01 09:34:00 Y,2005-02-02 08:45:00,Z,2005-02-02 09:30:00 Y,2005-02-03 08:48:00,Z,2005-02-03 09:36:00 Y,2005-02-04 08:50:00,Z,2005-02-04 09:33:00 Y,2005-02-07 08:47:00,Z,2005-02-07 09:36:00 Y,2005-02-08 08:41:00,Z,2005-02-08 09:22:00 Y,2005-02-09 08:46:00,Z,2005-02-09 09:31:00 Y,2005-02-10 08:48:00,Z,2005-02-10 09:35:00 Y,2005-02-11 08:53:00,Z,2005-02-11 09:34:00 Y,2005-02-14 09:18:00,Z,2005-02-14 10:33:00 Y,2005-02-15 08:53:00,Z,2005-02-15 09:45:00 Y,2005-02-16 08:45:00,Z,2005-02-16 09:29:00 Y,2005-02-17 08:38:00,Z,2005-02-17 09:24:00 Y,2005-02-18 0
