I have unevenly distributed data(wrt date) for a few years (2003-2008). I want to query data for a given set of start and end date, grouping the data by any of the supported intervals (day, week, month, quarter, year) in PostgreSQL 8.3 (http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC). The prob...
I've inherited a database that has a structure with a table of products, a table consisting of some product attributes and another table to build the relationship between these attributes and a given product. A user can filter the products by a combination of these attributes, meaning that if more than one attribute is selected only pro...
I have a dataset in MySQL where using limit is already an expensive query, and finding the number of results is expensive as well. Thus, I'd like to avoid doing another query to find the number of results. I cannot use MYSQL_CALC_FOUND_ROWS because the limit is inside a subquery: SELECT * FROM items, ( SELECT item_id FROM ...
I'll be using the AdventureWorks Database to illustrate my problem. I need to show for a particular customer a list of OrderDate with the most Orders. My intial attempt was as follows: SELECT CustomerID, OrderDate, COUNT(1) Cnt FROM Sales.SalesOrderHeader WHERE CustomerID = 11300 GROUP BY CustomerID, OrderDate ORDER BY Cnt DESC This...
Hi, I have a user table like this user_id | community_id | registration_date -------------------------------------------- 1 | 1 | 2008-01-01 2 | 1 | 2008-05-01 3 | 2 | 2008-01-28 4 | 2 | 2008-07-22 5 | 3 | 2008-01-11 For each community, I would like ...
Hi! I have a MySQL table LOGIN_LOG with fields ID, PLAYER, TIMESTAMP and ACTION. ACTION can be either 'login' or 'logout'. Only around 20% of the logins have an accompanying logout row. For those that do, I want to calculate the average duration. I'm thinking of something like select avg(LL2.TIMESTAMP - LL1.TIMESTAMP) from LOGIN_LOG L...
I have the following attributes in my DB. statistic id, devicename, value, timestamp. For a given statistic, I want to find the 2 most recent timestamps and corresopnding values for a every unique device. I am trying stuff like Trial 1) select statistic, devicename, value, timestamp from X_STATSVALUE where statistic=19 order by...
HI, I have a large table from which i can query to get the following table type no of times type occurs 101 450 102 562 103 245 also i can get another table code no of times code occurs 0 1222 1 750 2 355 but now i want to write a query which ...
Hi I'm using the content query web part and have exported it to a webpart file to allow me to change the queryoverride and groupby elements. I want to group the results alphabetically, so I thought that I can maybe use a function to grab the first letter of the title of the list items and then group by this, it might do the trick. Prob...
Hi Guys, One of my columns type is DateTime (Date Registered). I cannot create a query that filters all the data for eg. All registrations who registered on the 22/10/2008 between 18:00 and 20:00. Thanks ...
Hi, Previous programmer left me with "beautiful" piece of code and he kind of forgot to apply something to it. There is a query which selects several items from several tables. 6 Items can be chosen. It means 6 tables can be chosen, however there can be more tables - even 20 of them. I need to get that list of tables from processed que...
I'd like to implement a bloom filter using MySQL (other a suggested alternative). The problem is as follows: Suppose I have a table that stores 8 bit integers, with these following values: 1: 10011010 2: 00110101 3: 10010100 4: 00100110 5: 00111011 6: 01101010 I'd like to find all results that are bitwise AND to this: 00011000 Th...
I've got a table, called faq_questions with the following structure: id int not_null auto_increment, question varchar(255), sort_order int I'm attempting to build a query that given a sort order, selects the row with the next highest sort order. Example: id question sort_order 1 'This is question 1' 10 2 'Th...
I was browsing through the questions and noticed this: SELECT prodid, issue FROM Sales WHERE custid = @custid AND datesold = SELECT MAX(datesold) FROM Sales s WHERE s.prodid = Sales.prodid AND s.issue = Sales.issue AND s.custid = @custid I was wondering what the "@" does...
Hi, I have the following table custid ordid qty datesold 1 A2 12 2008-01-05 2 A5 5 2008-01-02 1 A1 5 2008-01-01 2 A7 3 2007-02-05 What't the best way of getting the previous order for every customer? Thanks ...
hi, i'm having an issue with creating a query in oracle which doesnt seem to want to join on missing values the table i have is this: table myTable(refnum, contid, type) values are: 1, 10, 90000 2, 20, 90000 3, 30, 90000 4, 20, 10000 5, 30, 10000 6, 10, 20000 7, 20, 20000 8, 30, 20000 a break down of the fields i'm after is this: ...
I need to query the table for "all but keyword". Using just "-" doesn't work, and in fact mysql manual says: Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by - returns an empty result. It does not return “all row...
Is there an easy or straightforward way in Java to output the results of a DB Query to a file (either csv, tab, etc). Perhaps even in Hibernate? I know that a query results can be dumped to a flat file on the DB Server. I am looking for a way that an application can run a query and get those results into a file. I realize one option ...
So, I have the following rows in the DB: 1 | /users/ 2 | /users/admin/ 3 | /users/admin/* 4 | /users/admin/mike/ 5 | /users/admin/steve/docs/ The input URL is /users/admin/steve/, and the goal is to find the URL match from the DB. I want to return #3 as the correct row, since the wildcard "*" specifies that anything can go in plac...
Hi, I have an articles table and a categories table. I want to fetch 7 articles for each category. Currently I have this but it's terrible slow on large tables so it's not really a solution: SELECT id, title, categories_id, body, DATE_FORMAT(pubdate, "%d/%m/%y %H:%i") as pubdate FROM articles AS t WH...