Select Inner.* from nested(outer)-nested(inner) query
in [Programming SQL]
|
From: MRe on 7 Jul 2010 08:26 Hi, Is it possible to "SELECT *" just the fields of a sub-nested query (i.e., I don't want * of a nested query, but of a nested-nested query)? For example (SELECT Inner.*): ---------------------------- SELECT Inner.* FROM ( SELECT Inner.* , ROW_NUMBER() OVER ( ORDER BY x ) AS ROW FROM ( SELECT x FROM y ) AS Inner WHERE ROW BETWEEN @Start AND @End ) AS Outer ---------------------------- So I want the result to be everything from the sub-nested query (and not the extra fields added in the nested query). It's an odd question, I'm sure, but I ask because I would like to automate web-site pagination, where I was thinking I could do it by wrapping any query in one that attaches row numbers for page filtering, and then another to return the query back to its original columns (while not having to actually know what columns they are) Thank you, Kind regards, Eliott
From: --CELKO-- on 7 Jul 2010 10:36 INNER and OUTER are reserved words in SQL. I would leave the row numbers in the result set; if you need to drop them, do it in the front end. (SELECT CoreQuery.*, ROW_NUMBER() OVER (ORDER BY x) AS display_position FROM (SELECT .. FROM .. WHERE ..) AS CoreQuery WHERE display_position BETWEEN in_start_display_position AND in_end_display_position); But you really ought to do pagination in the report server or front end and not the database. We were playing around with various ways to get a page number and line number within page. Here are some of the answers. None have been tested, so you might want to try them and see what happens: REATE TABLE Foobar (x INTEGER NOT NULL); GO SELECT * FROM Foobar; INSERT INTO Foobar VALUES (39634), (62349), (74088), (65564), (16379), (19713), (39153), (69459), (17986), (24537), (14595), (35050), (40469), (27478), (44526), (67331), (93365), (54526), (22356), (93208), (30734), (71571), (83722), (79712), (25775), (65178), (07763), (82928), (31131), (30196), (64628), (89126), (91254), (24090), (25752), (03091), (39411), (73146), (06089), (15630), (42831), (95113), (43511), (42082), (15140), (34733), (68076), (18292), (69486), (80468), (80583), (70361), (41047), (26792), (78466), (03395), (17635), (09697), (82447), (31405), (00209), (90404), (99457), (72570), (42194), (49043), (24330), (14939), (09865), (45906), (05409), (20830), (01911), (60767), (55248), (79253), (12317), (84120), (77772), (50103), (95836), (22530), (91785), (80210), (34361), (52228), (33869), (94332), (83868), (61672), (65358), (70469), (87149), (89509), (72176), (18103), (55169), (79954), (72002), (20582), (72249), (04037), (36192), (40221), (14918), (53437), (60571), (40995), (55006), (10694), (41692), (40581), (93050), (48734), (34652), (41577), (04631), (49184), (39295), (81776), (61885), (50796), (96822), (82002), (07973), (52925), (75467), (86013), (98072), (91942), (48917), (48129), (48624), (48248), (91465), (54898), (61220), (18721), (67387), (66575), (88378), (84299), (12193), (03785), (49314), (39761), (99132), (28775), (45276), (91816), (77800), (25734), (09801), (92087), (02955), (12872), (89848), (48579), (06028), (13827), (24028), (03405), (01178), (06316), (81916), (40170), (53665), (87202), (88638), (47121), (86558), (84750), (43994), (01760), (96205), (27937), (45416), (71964), (52261), (30781); */ BEGIN DECLARE @page_size INTEGER; SET @page_size = 8; -- whatever SELECT x,(ROW_NUMBER() OVER (ORDER BY x)) AS absolute_line_nbr, (((ROW_NUMBER() OVER (ORDER BY x) + (@page_size -1))/ @page_size)) AS page_number, CASE WHEN (ROW_NUMBER() OVER (ORDER BY x) % @page_size) > 0 THEN (ROW_NUMBER() OVER (ORDER BY x) % @page_size) ELSE @page_size END AS line_number_within_page_1, COALESCE (NULLIF (ROW_NUMBER() OVER (ORDER BY x) % @page_size, 0), @page_size) AS line_number_within_page_2, @page_size + (ROW_NUMBER() OVER (ORDER BY x) - @page_size* CEILING ((ROW_NUMBER() OVER (ORDER BY x))/ (1.0 * @page_size))) AS line_number_within_page_3 FROM Foobar; --totally different approach SELECT x, page_nbr, ROW_NUMBER() OVER (PARTITION BY page_nbr ORDER BY x) AS line_nbr FROM (SELECT x, ((ROW_NUMBER() OVER (ORDER BY x) + (@page_size -1))/ @page_size) AS page_nbr FROM Foobar) AS Pages; END;
From: MRe on 7 Jul 2010 11:12 Hi CELKO, Thank you for the response. > INNER and OUTER are reserved words in SQL. Sorry, yes - I should be more careful with these things. Thanks for the heads-up. > I would leave the row numbers in the result set; if you need to drop them, do it in the front end. I was hoping to avoid this so as not to upset the other developers (as it'll be auto-generating the modifying query behind the scenes). I suppose if it can't be done, it's not too big a price > But you really ought to do pagination in the report server or front end and not the database We had been doing pagination in the front-end before, and moved away as it seemed like a bad idea because we had to hold on to the whole record-set in session memory. Plus, as there are usually more pages available for viewing than are actually viewed, it was wasteful. Why do you say it should be done at the front-end? > We were playing around with various ways to get a page number and line number within page. Here are some of the > answers. Interesting. I had been working out page number (in my auto-generating way) by wrapping the original query in a "SELECT COUNT(*) FROM (<original query>) AS TotalEntries" and calculating the number of pages in the front-end, but these methods look good. I will certainly given them a try. Thank you kindly, Eliott
From: --CELKO-- on 7 Jul 2010 12:16 >> Why do you say it should be done at the front-end? << 1) In a tiered architecture, formatting is done in the front end and not the DB. 2) I am used to having a report server. You write the basic query once, throw it over the wall to the report server where it is sorted and aggregated many different ways (totals by product, totals by region, etc), sent out as emails (hre is your regional report), turned into graphics (It must have a dancing bear in the top left corner of the spreadsheet), and so forth. This is SOOOO much faster and safer than having a ton of DB side routines for each report. >> these methods look good. I will certainly given them a try. << We have no timings on them --they were a programming exercise. Let me know how theyw ork.
From: Erland Sommarskog on 7 Jul 2010 17:13
MRe (pgdown(a)gmail.com) writes: > Is it possible to "SELECT *" just the fields of a sub-nested query > (i.e., I don't want * of a nested query, but of a nested-nested > query)? No. You could do SELECT INTO a temp table, and then drop the row_number column from the temp table, and then do SELECT * from the temp table. But don't this, as this can have some performance implications on a busy system. > It's an odd question, I'm sure, but I ask because I would like to > automate web-site pagination, where I was thinking I could do it by > wrapping any query in one that attaches row numbers for page > filtering, and then another to return the query back to its original > columns (while not having to actually know what columns they are) Either you pass the row number to the application (which probably can find use for it), or list the columns explicitly. SELECT * is after all considered bad practice. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |