populating mysql database
I have a file with over a million lines of data, each line is a record. I can go through the file, read the line and do a insert, but this can take up to 2 hours. Is there a faster way like uploading a sql file? ...
query
Selecting the latest per group of items
i have 2 tables products and cost PRODUCT ProdCode - PK ProdName COST Effectivedate - PK RetailCOst Prodcode i tried this query: SELECT a.ProdCOde AS id, MAX(EffectiveDate) AS edate, RetailCOst AS retail FROM cost a INNER JOIN product b USING (ProdCode) WHERE EffectiveDate <= '2009-10-01' GROUP BY a.ProdCode; uhm yah its show...
Show only Previous Month's data in SQL
I have been told to create a query that will show the top 10 most used applications in our company, and on the 11th row should group all other rows into it and call the record "Other" and sum all the sessions together. How do I modify this code to only show monthly records? Code Snippet SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) ...
How should i modify this SQL select?
SELECT TOP (5) mydb.Te.TeamGR AS TeamInGR, Te_1.TeamGR, SUBSTRING(mydb.Data.AkrivesSkor, 1, 1) AS GoalsIn, SUBSTRING(mydb.Data.AkrivesSkor, 3, 1) AS GoalsOut FROM mydb.Te INNER JOIN mydb.Data ON mydb.Te.TeamID = mydb.Data.TeamInID INNER JOIN mydb.Diorganoseis ...
SQL function to sort by most popular content
I don't know if this is possible with SQL: I have two tables, one of content, each with an integer ID, and a table of comments each with an "On" field denoting the content it is on. I'd like to receive the content in order of how many comments have it in their "On" field, and was hoping SQL could do it. ...
Some help with hibernate Criteria query
Can somebody please help me complete / clean up this query. I am trying to first get the number of rows in the table, then I need to actually fetch a set of rows. I am not sure how I can use the same criteria instance to perform both queries. To fetch the count I have something like this: Criteria criteria = session.createCriteria(My...
customize filter or property in sqlalchemy
I'm using the SQLAlchemy ORM to work with both MySQL and sqlite. Suppose I have a function that will take input from a column of string type and finally return an integer. The logic of this function may be complicated that can not be implemented by simply using built-in SQL functions provided by back-end database engines. I am wondering...
SQL Group Select Query
Hi, I have the following table: ----------- ---------- ----------- | AccountID | Password | IpAddress | ----------- ---------- -----------| | 1 1234 127.0.0.1 | | 2 123 127.0.0.1 | | 3 1234 127.0.0.1 | | 4 12 127.0.0.2 | | 5 123 127.0.0.2 | | 6 ...
Next previous links from a query set / generic views
I have a quite simple query set and a related generic views: f_detail = { 'queryset': Foto.objects.all(), 'template_name': 'foto_dettaglio.html', "template_object_name" : "foto", } urlpatterns = patterns('', # This very include (r'^foto/(?P<object_id>\d+)/$', list_detail.object_detail, f_detail, ), ) ...
Advanced SQL query. Top 12 from each category (MYSQL)
I have a MYSQL5 database and PHP 5. I need a query for a games websites index page that only selects the first 12 from each category of games. Here is what I have so far. $db->query("SELECT * FROM `games` WHERE status = 'game_published' AND `featured` = '1' ORDER BY `category`"); The php code then groups games of the same category ...
JPA Native Query to tables from different schema in Oracle
I have table MV_ULICE in schema ADRESY. However in JPA I connect to database (Oracle) using different user then ADRESY. This user has privilege to access tables from schema ADRESY, so in JPA there was no problem with defining entities, since you can easily provide different schema in entity definition: @Entity @Table(name = "MV_ULICE", ...
List items by category
Hello I have 3 tables: category doesnt matter item2cat: itemID|catID item: id|name|desc I want to list the items that are in the given category, but I don't know how to do it in a simple way. (With PHP and MySQL) I need this table-structure because I want multiple categories to one item. ...
concurrent mySql database queries
Hi I'm running mySql server that being updated every 4 hours. In the mean while data can be retrieved. Does mySql handle this scenario where the DB is being updated and a query from the user is received? or I should handle this scenario? Is it possible to create a snapshot of the DB just before the update takes place and query this D...
Mysql Average on time column?
Hi i have this query: SELECT avg( duration ) as average FROM `login`; The datatype for duration is "time", thus my value is like: 00:00:14, 00:20:23 etc I execute the query it gives me: 2725.78947368421 What is that? I want in time format, can mysql do the average on time?? ...
Django: limit queryset to a condition in the latest instance of a related set
Hi! I have an hierarchy of models that consists of four levels, all for various good reasons but which to explain would be beyond the scope of this question, I assume. So here it goes in pseudo python: class Base(models.Model): ... class Top(models.Model): base = FK(Base) class Middle(models.Model): top = FK(Top) crea...
sql query date time
i have a datestamp field in my table structure. using sql, i want to find any user who registered in yesterdays date using these time range. eg: 2010-02-06 14:00:00 2010-02-07 10:00:00 i will be running this query once a day to grab users. so tomorrow will be: 2010-02-07 14:00:00 2010-02-08 10:00:00 the day after tomorrow will be: ...
Problem in Retrieving records by giving from and to date
Hi all, I have a Problem in Retrieving records by giving from and to date. I want to fetch record for date Feb 08 2010, and I should use To and From date fields. I tried: select * from dbo.BuzzMaster where date >= '2/7/2010' and date <= '2/8/2010' Output: Not able to retrieve records for date '2/8/2010' I get the record for d...
Querying large XML file (600mb+) in PHP or JavaScript?
I have a large XML file (600mb+) and am developing a PHP application which needs to query this file. My initial approach was to extract all the data from the file and insert it into a MySQL database - then query it that way. The only issue with this was that it was still slow, plus the XML data gets updated regularly - meaning I need to...
Difficult query (DB2)
Suppose I have a table called spitems with the following fields: spitemid (unique key) modifiedon (timestamp) parentid a number of other unsignificant fields What I want to retrieve, is the spitem rows with the highest modifiedon day for each parentid. However, be aware that the modifiedon timestamp is not unique, so it is possible ...
Using Hibernate to do a query involving two tables
I'm inexperienced with sql in general, so using Hibernate is like looking for an answer before I know exactly what the question is. Please feel free to correct any misunderstandings I have. I am on a project where I have to use Hibernate. Most of what I am doing is pretty basic and I could copy and modify. Now I would like to do some...