I have a list of players in a tournament.  tournament=&gt; SELECT * FROM players;        name        | player_id -------------------+-----------  Twilight Sparkle  |         9  Fluttershy        |        10  Applejack         |        11  Pinkie Pie        |        12  Rarity            |        13  Rainbow Dash      |        14  Princess Celestia |        15  Princess Luna     |        16 (8 rows)   This is how I would like the list to look. How do I ask postgreSQL to make it so?         name1       |   id1   |       name2       |   id2 -------------------+---------+-------------------+------- Twilight Sparkle   |    9    |  Fluttershy       |    10 Applejack          |    11   |  Pinkie Pie       |    12 Rarity             |    13   |  Rainbow Dash     |    14 Princess Celestia  |    15   |  Princess Luna    |    16 (4 pairs)
17043
I have a Python script which takes as input a list of integers, which I need to work with four integers at a time.  Unfortunately, I don't have control of the input, or I'd have it passed in as a list of four-element tuples.  Currently, I'm iterating over it this way: for i in range(0, len(ints), 4):     # dummy op for example code     foo += ints[i] * ints[i + 1] + ints[i + 2] * ints[i + 3]  It looks a lot like &quot;C-think&quot;, though, which makes me suspect there's a more pythonic way of dealing with this situation.  The list is discarded after iterating, so it needn't be preserved.  Perhaps something like this would be better? while ints:     foo += ints[0] * ints[1] + ints[2] * ints[3]     ints[0:4] = []  Still doesn't quite &quot;feel&quot; right, though.  :-/ Related question:
28370
I have a table similar to this:  CREATE TABLE example (   id integer primary key,   name char(200),   parentid integer,   value integer);   I can use the parentid field to arrange data into a tree structure.  Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?  UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.  BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!
88523
I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this.  this will be a little long, be patient please.  First of all, I am trying to make a simple lottery app. I hope everyone of you know about the 6/49 lottery thing.  Well, according to game, first, for a week customers play lottery (lotteryTBL). Then 6 numbers between 1-49 are selected as lucky numbers (resultsTBL).  What I want to do is updating lotteryTBL after a new result row is inserted into resultsTBL.  Structure of lotteryTBL:  ID    num1  num2  num3  num4  num5  num6     draw_date      howmanyknew  1       5    6     8    15      18    25     08-21-2015         0 2       7    15    18   30      40    45     08-21-2015         0   Structure of resultsTBL:  ID    num1  num2  num3  num4  num5  num6     draw_date   1      15    18    35    38    40    47     08-21-2015    Now, here is the pseudocode of what i want:  after insert a new row into resultsTBL    total &lt;- 0    for each row in the lotteryTBL where lotteryTBL.draw_date = resultsTBL.draw_date        total &lt;- calculate how many of them are the same with the resultsTBL's row.    update lotteryTBL set howmanyknew = total where "some condition"   First, I tried to do it with trigger, some said "it is dynamic sql, you cannot use trigger", some said "use stored procedure". And I did it too. But, they never worked.  Anyway, Can someone type a good pseudocode for this? which method do I have to use? what is the logic here?
3504
I've got the following two tables (in MySQL): Phone_book +----+------+--------------+ | id | name | phone_number | +----+------+--------------+ | 1  | John | 111111111111 | +----+------+--------------+ | 2  | Jane | 222222222222 | +----+------+--------------+  Call +----+------+--------------+ | id | date | phone_number | +----+------+--------------+ | 1  | 0945 | 111111111111 | +----+------+--------------+ | 2  | 0950 | 222222222222 | +----+------+--------------+ | 3  | 1045 | 333333333333 | +----+------+--------------+  How do I find out which calls were made by people whose phone_number is not in the Phone_book? The desired output would be: Call +----+------+--------------+ | id | date | phone_number | +----+------+--------------+ | 3  | 1045 | 333333333333 | +----+------+--------------+
115109
Consider this table in mysql  create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers;  +--------+ | number | +--------+ |      3 | |      2 | |      9 | +--------+   Is there a simple query to generate a table with the following columns   The numbers from 1 till 10 1 if the number is present in the table numbers, and 0 otherwise   I guess you have to create a sequence of numbers in order to do this. If possible, I want to create such a sequence without storing it in the database.  Related question: is there a select query that generates a sequence of numbers from 1 till 10 (or 100 or 1000)?
52652
I don’t know how to handle this 3 players case, may anyone give a hand? Thanks!     When playing card game, there are 3 players playing 32 cards, each   receive 10 cards, 2 remind on the table. Overall are 4 aces and 4   kings in the game. How likely is P (E | D)? D = "The table remains 2   aces" E = "I (at least) get one king".   I guess I should have steps as follows.   P(E) = P(1King)+P(2K)+P(3K)+P(4K) P(D) = (4/32) * (4/32) = 1/256 P (E | D) = P(E) + P(D)   Equations may be used:  Counting combinations      The number of combinations of n objects taken r at a time is     nCr = n(n - 1)(n - 2) . . . (n - r + 1)/r! = n! / r!(n - r)!                 Number of favorable outcomes P(I) =     Total number of possible outcome
25040
I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question).  CREATE TABLE  `clients` (   `clientId` int(10) unsigned NOT NULL auto_increment,   `clientName` varchar(100) NOT NULL default '',   `courseNames` varchar(255) NOT NULL default '' )   The courseNames field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"  CREATE TABLE  `clientenrols` (   `clientEnrolId` int(10) unsigned NOT NULL auto_increment,   `studentId` int(10) unsigned NOT NULL default '0',   `courseId` tinyint(3) unsigned NOT NULL default '0' )   The courseId field here is the index of the course name in the clients.courseNames field. So, if the client's courseNames are "AB01,AB02,AB03", and the courseId of the enrolment is 2, then the student is in AB03.  Is there a way that I can do a single select on these tables that includes the course name? Keep in mind that there will be students from different clients (and hence have different course names, not all of which are sequential,eg: "NW01,NW03")  Basically, if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode:  SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`] FROM ...
17868
how do you count the number of columns in a table in oracle?
67864
Completely shuffle and then lay out a full deck of 52 cards in a single row (i.e. single row 52 columns). Do the same thing with a second deck of playing cards and place this deck in the second row(also has 52 columns). What is the expected total number of matches in all the columns?   A match means that the top card and the bottom card in the column are the same (same rank and suit).  I was trying to solve it by brute force but seems like it's not that easy to me.
28961
Table #01 Status:  StatusID    Status -----------------------  1          Opened  2          Closed  3          ReOpened  4          Pending   Table #02 Claims:  ClaimID     CompanyName StatusID -------------------------------------- 1               ABC     1 2               ABC     1 3               ABC     2 4               ABC     4 5               XYZ     1 6               XYZ     1   Expected Result:  CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims -------------------------------------------------------------------------------- ABC                 2           1                      0               1 XYZ                 2           0                      0               0   How do I need to write the query so that I could get the result as expected?
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?
5024
Problem: Find the number of twelve-card hands containing four cards in each of three suits.   My solution:   pick a suit : 4 ways. choose 4 cards from the 13 in that suit: ${^{13}}{C}_{4}$ ways. pick another suit: 3 ways. choose 4 cards from the 13 in the new suit: ${^{13}}{C}_{4}$ ways. pick the final suit: 2 ways. choose 4 cards from the 13 in the final suit: ${^{13}}{C}_{4}$ ways.   Hence, total number of ways is $4\cdot{^{13}}{C}_{4}\cdot3\cdot{^{13}}{C}_{4}\cdot2\cdot{^{13}}{C}_{4} = {^{4}}{P}_{3}\cdot\left({^{13}}{C}_{4}\right)^3 $ total hands.   This is the incorrect answer. Why?
118306
I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values.  This is identical to , but for Oracle rather than SQL Server or MySQL.
112994
I have a balances table in PostgreSQL 9.3 that looks like this:  CREATE TABLE balances (   user_id INT , balance INT , as_of_date DATE );  INSERT INTO balances (user_id, balance, as_of_date) VALUES   (1, 100, '2016-01-03') , (1,  50, '2016-01-02') , (1,  10, '2016-01-01') , (2, 200, '2016-01-01') , (3,  30, '2016-01-03');   It only contains balances for dates that a user has made a transaction. I need it to contain a row for each user with their balance on each date in a given date range.   If the user does not have a row for a given date in the range, I need to use their balance from the day before. If the user created their account after a given date in the range, I need to avoid creating a row for that user/date combination.   I can reference an accounts table to get users' create_date:  CREATE TABLE accounts (   user_id INT , create_date DATE );  INSERT INTO accounts (user_id, create_date) VALUES   (1, '2015-12-01') , (2, '2015-12-31') , (3, '2016-01-03');   My desired result looks like this:  +---------+---------+--------------------------+ | user_id | balance |        as_of_date        | +---------+---------+--------------------------+ |       1 |     100 | 2016-01-03T00:00:00.000Z | |       1 |      50 | 2016-01-02T00:00:00.000Z | |       1 |      10 | 2016-01-01T00:00:00.000Z | |       2 |     200 | 2016-01-03T00:00:00.000Z | |       2 |     200 | 2016-01-02T00:00:00.000Z | |       2 |     200 | 2016-01-01T00:00:00.000Z | |       3 |      30 | 2016-01-03T00:00:00.000Z | +---------+---------+--------------------------+   Note that rows have been added for user 2 for 2016-01-02 and 2016-01-03, carrying over the previous balance from 2016-01-01; and that no rows have been added for user 3, who was created on 2016-01-03.  To generate a series of dates in a date range, I know I can use:  SELECT d.date FROM GENERATE_SERIES('2016-01-01', '2016-01-03', '1 day'::INTERVAL) d   ...but I am struggling with LEFT JOINing that series with each set of rows grouped by user_id.
64714
I have the following table:  NAME Alex Bob  Bob Tim Alex Roger   I need to produce the following:   Name    Count Percentage  Alex    2     33%  Bob     2     33%  Roger   1     16.6%  Tim     1     16.6%   I have no idea where to start.  I can do the initial query for the count which is simple.
92183
I've got about a billion rows of data in a table with a name and an integer in the range 1-288.  For a given name, every int is unique, and not every possible integer in the range is present--so there are gaps.  This query generates an example case:  --what I have: SELECT * FROM ( VALUES ('foo', 2),               ('foo', 3),               ('foo', 4),               ('foo', 10),               ('foo', 11),               ('foo', 13),               ('bar', 1),               ('bar', 2),               ('bar', 3)      ) AS baz ("name", "int")   I'd like to generate a lookup table with a row for each name and sequence of contiguous integers.  Each such row would contain:  name   --  the value of the name column start  --  the first integer in the contiguous sequence end    --  the final value in the contiguous sequence span   --  end - start + 1  This query generates example output for the above example:  --what I need: SELECT *  FROM ( VALUES ('foo', 2, 4, 3),               ('foo', 10, 11, 2),               ('foo', 13, 13, 1),               ('bar', 1, 3, 3)      ) AS contiguous_ranges ("name", "start", "end", span)   Because I have so many rows, more efficient is better.  That said, I only have to run this query once, so it isn't an absolute requirement.  Thanks in advance!  Edit:  I should add that PL/pgSQL solutions are welcome (please explain any Fancy Tricks--I'm still new to PL/pgSQL).
12844
Using MySQL, I can do something like:  SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;   My Output:  shopping fishing coding   but instead I just want 1 row, 1 col:  Expected Output:  shopping, fishing, coding   The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.  I've looked for a function on  and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets.  So does anyone here know how to do this?