Search Results

Search found 27497 results on 1100 pages for 'sql joke'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 626/1100 | < Previous Page | 622 623 624 625 626 627 628 629 630 631 632 633  | Next Page >

  • Optimizing MySQL statement with lot of count(row) an sum(row+row2)...

    - by Zombies
    I need to use InnoDB storage engine on a table with about 1mil or so records in it at any given time. It has records being inserted to it at a very fast rate, which are then dropped within a few days, maybe a week. The ping table has about a million rows, whereas the website table only about 10,000. My statement is this: select url from website ws, ping pi where ws.idproxy = pi.idproxy and pi.entrytime > curdate() - 3 and contentping+tcpping is not null group by url having sum(contentping+tcpping)/(count(*)-count(errortype)) < 500 and count(*) > 3 and count(errortype)/count(*) < .15 order by sum(contentping+tcpping)/(count(*)-count(errortype)) asc; I added an index on entrytime, yet no dice. Can anyone throw me a bone as to what I should consider to look into for basic optimization of this query. The result set is only like 200 rows, so I'm not getting killed there.

    Read the article

  • How to Generate XML from Database

    - by Nisarg Mehta
    Hi , I am fetching data from two tables CARRIER_IFTA ,IFTA_NAME. My Select Query is like below.. SELECT t1.IFTA_LICENSE_NUMBER,t1.IFTA_BASE_STATE,t2.NAME_TYPE,t2.NAME from CARRIER_IFTA t1 inner join IFTA_NAME t2 on t1.IFTA_LICENSE_NUMBER=t2.IFTA_LICENSE_NUMBER My Data is coming in this way... IFTA_LICENSE_NUMBER IFTA_BASE_STATE NAME_TYPE NAME -------------------------------------------------------- 630908333 US LG XYZ 630908333 US MG PQR 730908344 US LG ABC Now using XSLT I want to generate XML like this <T0019> <IFTA_ACCOUNT> <IFTA_LICENSE_NUMBER>630908333</IFTA_LICENSE_NUMBER> <IFTA_BASE_STATE>US</IFTA_BASE_STATE> <IFTA_NAME> <NAME_TYPE>LG<NAME_TYPE> <NAME>XYZ</NAME> </IFTA_NAME> <IFTA_NAME> <NAME_TYPE>MG<NAME_TYPE> <NAME>PQR</NAME> <IFTA_NAME> </IFTA_ACCOUNT> <IFTA_ACCOUNT> <IFTA_LICENSE_NUMBER>730908344</IFTA_LICENSE_NUMBER> <IFTA_BASE_STATE>US</IFTA_BASE_STATE> <IFTA_NAME> <NAME_TYPE>LG<NAME_TYPE> <NAME>ABC</NAME> </IFTA_NAME> </IFTA_ACCOUNT> </T0019> i have used below xslt but it is not giveng me desire result ... <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0"> <xsl:template match="/ROWSET"> <xsl:element name="T0019"> <xsl:apply-templates select="IFTAACCOUNT"/> </xsl:element> </xsl:template> <xsl:template match="IFTAACCOUNT"> <xsl:element name="IFTAACCOUNT"> <xsl:apply-templates select="IFTA_CARRIER_ID_NUMBER"/> </xsl:element> </xsl:template> <xsl:template match="IFTA_LICENSE_NUMBER"> <xsl:element name="IFTA_LICENSE_NUMBER"> <xsl:apply-templates /> </xsl:element> </xsl:template> <xsl:template match="IFTA_BASE_STATE"> <xsl:element name="IFTA_BASE_STATE"> <xsl:apply-templates /> </xsl:element> </xsl:template> <xsl:template match="IRP_NAME"> <IRP_NAME> <xsl:apply-templates select="NAME"/> <xsl:apply-templates select="NAME_TYPE"/> </IRP_NAME> </xsl:template> <xsl:template match="NAME"> <xsl:element name="NAME"> <xsl:value-of select="." /> </xsl:element> </xsl:template> <xsl:template match="NAME_TYPE"> <xsl:element name="NAME_TYPE"> <xsl:apply-templates /> </xsl:element> </xsl:template> </xsl:stylesheet> but it is not giving desire result ... Please help me ... Thanks in Advance...

    Read the article

  • MySQL Count If using 4 tables or Perl

    - by user1726133
    Hi I have a relatively convoluted query that relies on 4 different tables, unfortunately I do not have control of this data, but I do have to query it. I ran this simpler query and it works using just table 1 and table 2 SELECT actor, receiver, count(IF(t2.group1 = "anxiety behavior", 1,0)) AS 'anxiety' FROM ethogram_edited_obs_behaviors t1 JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code GROUP BY actor; Below are the 4 tables I need and the query I tried that didn't work Table 1 | Table 2 | Table 3 | Table 4 Actor | Behavior | Behavior | type of Behavior | subject | sex | subject |subject_code er frown | frown anxiety behavior | Eric M | Eric | er Here is the query that is failing SELECT actor, count(IF(t2.group1 = "anxiety behavior", 1,0) AND(t3.sex = "M", 1,0)) AS 'anxiety', FROM ethogram_edited_obs_behaviors t1 JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code JOIN subject_code t3 on t1.actor = t3.behavior_code1 JOIN subjects t4 on t3.subject = t4.yerkes_code GROUP BY actor; Any help would be much appreciated!! Thanks :) P.S. if this is easier to do in Perl tips also much appreciated

    Read the article

  • Auto increment with a Unit Of Work

    - by Derick
    Context I'm building a persistence layer to abstract different types of databases that I'll be needing. On the relational part I have mySQL, Oracle and PostgreSQL. Let's take the following simplified MySQL tables: CREATE TABLE Contact ( ID varchar(15), NAME varchar(30) ); CREATE TABLE Address ( ID varchar(15), CONTACT_ID varchar(15), NAME varchar(50) ); I use code to generate system specific alpha numeric unique ID's fitting 15 chars in this case. Thus, if I insert a Contact record with it's Addresses I have my generated Contact.ID and Address.CONTACT_IDs before committing. I've created a Unit of Work (amongst others) as per Martin Fowler's patterns to add transaction support. I'm using a key based Identity Map in the UoW to track the changed records in memory. It works like a charm for the scenario above, all pretty standard stuff so far. The question scenario comes in when I have a database that is not under my control and the ID fields are auto-increment (or in Oracle sequences). In this case I do not have the db generated Contact.ID beforehand, so when I create my Address I do not have a value for Address.CONTACT_ID. The transaction has not been started on the DB session since all is kept in the Identity Map in memory. Question: What is a good approach to address this? (Avoiding unnecessary db round trips) Some ideas: Retrieve the last ID: I can do a call to the database to retrieve the last Id like: SELECT Auto_increment FROM information_schema.tables WHERE table_name='Contact'; But this is MySQL specific and probably something similar can be done for the other databases. If do this then would need to do the 1st insert, get the ID and then update the children (Address.CONTACT_IDs) – all in the current transaction context.

    Read the article

  • Add LINQ Auto-Generated Value Marker [Column(IsDbGenerated=true)] in Buddy Class

    - by Alex
    Hello, is it possible to decorate a field of a LINQ generated class with [Column(IsDbGenerated=true)] using a buddy class (which is linked to the LINQ class via [MetadataType(typeof(BuddyMetadata))]) ? My goal is to be able to clear and repopulate the LINQ ORM designer without having to set the "Auto Generate Value" property manually every time to re-establish the fact that certain columns are autogenerated. Thanks!

    Read the article

  • Oracle query to select rows with unique code

    - by Jasim
    I have a table like this C1 C2 C3 Code 1 2 3 33 1 2 3 34 2 4 1 14 1 2 3 14 i want to select only those record whose code is appearing only in single row. ie, in this case rows with code 33 and 34.. as they appear only once in this table. How can i write a query for that

    Read the article

  • mysqli_stmt_bind_param SQL Injection

    - by profitphp
    Is there still an injection risk when using prepared statements and mysqli_stmt_bind_param? For example: $malicious_input = 'bob"; drop table users'; mysqli_stmt_bind_param($stmt, 's', $malicious_input); Behind the scenes does mysqli_stmt_bind_param pass this query string to mysql: SET @username = "bob"; drop table users"; Or does it perform the SET command through the API, or use some type of protection to keep this from happening?

    Read the article

  • Copying 6000 tables and data from sqlserver to oracle ==> fastest method?

    - by nazer555
    i need to copy the tables and data (about 5 yrs data, 6200 tables) stored in sqlserver, i am using datastage and odbc connection to connect and datstage automatically creates the table with data, but its taking 2-3 hours per table as tables are very large(0.5 gig, 300+columns and about 400k rows). How can i achieve this the fastes as at this rate i am able to only copy 5 tables per day but within 30 days i need move over these 6000 tables.

    Read the article

  • Search a string to find which records in table are inside said string

    - by Improfane
    Hello, Say I have a string. Then I have a number of unique tokens or keywords, potentially a large number in a database. I want to search and find out which of these database strings are inside the string I provide (and get the IDs of them). Is there a way of using a query to search the provided string or must it be taken to application space? Am I right in thinking that this is not a 'full text search'? Would the best method be to insert it into the database to make it a full text search?

    Read the article

  • MySQL Delete from 1 table, using multiple tables

    - by nute
    I would like to delete all the rows found by that query: SELECT cart_abandon.* FROM cart_abandon, cart_product, txn_product, users WHERE cart_abandon.cartid = cart_product.cartid AND cart_product.productid = txn_product.productid AND txn_product.username = users.username AND users.id = cart_abandon.userid AND txn_product.txndate >= cart_abandon.abandondate The thing to keep in mind is that the query here uses 4 different tables, however I only want to delete rows from 1 table (cart_abandon). Is there an easy way to do that? Maybe this: ? DELETE cart_abandon FROM cart_abandon, cart_product, txn_product, users WHERE cart_abandon.cartid = cart_product.cartid AND cart_product.productid = txn_product.productid AND txn_product.username = users.username AND users.id = cart_abandon.userid AND txn_product.txndate >= cart_abandon.abandondate Is that valid? Correct?

    Read the article

  • Django: Determining if a user has voted or not

    - by TheLizardKing
    I have a long list of links that I spit out using the below code, total votes, submitted by, the usual stuff but I am not 100% on how to determine if the currently logged in user has voted on a link or not. I know how to do this from within my view but do I need to alter my below view code or can I make use of the way templates work to determine it? I have read http://stackoverflow.com/questions/1528583/django-vote-up-down-method but I don't quite understand what's going on ( and don't need any ofjavascriptery). Models (snippet): class Link(models.Model): category = models.ForeignKey(Category, blank=False, default=1) user = models.ForeignKey(User) created = models.DateTimeField(auto_now_add=True) modified = models.DateTimeField(auto_now=True) url = models.URLField(max_length=1024, unique=True, verify_exists=True) name = models.CharField(max_length=512) def __unicode__(self): return u'%s (%s)' % (self.name, self.url) class Vote(models.Model): link = models.ForeignKey(Link) user = models.ForeignKey(User) created = models.DateTimeField(auto_now_add=True) def __unicode__(self): return u'%s vote for %s' % (self.user, self.link) Views (snippet): def hot(request): links = Link.objects.select_related().annotate(votes=Count('vote')).order_by('-created') for link in links: delta_in_hours = (int(datetime.now().strftime("%s")) - int(link.created.strftime("%s"))) / 3600 link.popularity = ((link.votes - 1) / (delta_in_hours + 2)**1.5) if request.user.is_authenticated(): try: link.voted = Vote.objects.get(link=link, user=request.user) except Vote.DoesNotExist: link.voted = None links = sorted(links, key=lambda x: x.popularity, reverse=True) links = paginate(request, links, 15) return direct_to_template( request, template = 'links/link_list.html', extra_context = { 'links': links, }) The above view actually accomplishes what I need but in what I believe to be a horribly inefficient way. This causes the dreaded n+1 queries, as it stands that's 33 queries for a page containing just 29 links while originally I got away with just 4 queries. I would really prefer to do this using Django's ORM or at least .extra(). Any advice?

    Read the article

  • How to join nearly identical several queries into one?

    - by Devyn
    Hi, Assume I have an order_dummy table where order_dummy_id, order_id, user_id, book_id, author_id are stored. You may complain the logic of my table but I somehow need to do it that way. I want to execute following queries. SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 AND book_id = 1 ORDER BY `order_dummy_id` DESC LIMIT 1 SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 AND book_id = 2 ORDER BY `order_dummy_id` DESC LIMIT 1 SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 AND book_id = 3 ORDER BY `order_dummy_id` DESC LIMIT 1 Please keep in mind that several numbers of same book is included in one order. Therefore, I list order_dummy_id by descending and limit 1 so only LATEST ORDER of A BOOK is shown. But my goal is to show other books in that way in one table. I used group by like this ... SELECT * FROM order_dummy WHERE order_id = 1 AND user_id = 1 GROUP BY book_id but it only shows order_dummy_id with ascending result. I have no idea anymore. Looking forward your kindness help!

    Read the article

  • How to apply GROUP_CONCAT in mysql Query

    - by Query Master
    How to apply GROUP_CONCAT in this Query if you guys have any idea or any alternate solution about this please share me. Helps are definitely appreciated also (see Query or result required) Query SELECT WEEK(cpd.added_date) AS week_no,COUNT(cpd.result) AS death_count FROM cron_players_data cpd WHERE cpd.player_id = 81 AND cpd.result = 2 AND cpd.status = 1 GROUP BY WEEK(cpd.added_date); Query output result screen Result Required 23,24,25 AS week_no 2,3,1 AS death_count

    Read the article

  • Get column of a mysql entry

    - by Xelluloid
    Is there a possibility to get the name of the column a database entry belongs to? Perhaps I have three columns with column names col1, col2 and col3. Now I want to select for every column the column with the maximum entry, something like this. Select name_of_column(max(col1,col2,col3)). I know that I can ask for the name of the columns by its ordinal position in the information_schema.COLUMNS table but how do I get the ordinal position of a database entry within a table?

    Read the article

  • Getting a ReturnValue from cmd.Parameters in c#?

    - by mark smith
    Hi, I have just finished converted a vb.net app to c# and one of the lines is to get @ReturnValue from the parameter. I ended up having to CAST a lot of things.. Is there not a easier way here is what i have int rc = ((System.Data.SqlTypes.SqlInt32)(((System.Data.SqlClient.SqlParameter)(cmd.Parameters["@ReturnValue"])).SqlValue)).Value; In vb.net it was as simple as doing this Dim rc As Integer = Convert.ToInt32(cmd.Parameters("@ReturnValue").Value) Alot easier :-) But the problem with C# is the property Value isn't available unless I Cast to SqlParameter and i also need to cast to Sqltypes.SqlInt32 - i can't just do a standard Convert.ToInt32

    Read the article

  • Linq2Sql: query - subquery optimisation

    - by Budda
    I have the following query: IList<InfrStadium> stadiums = (from sector in DbContext.sectors where sector.Type=typeValue select new InfrStadium(sector.TeamId) ).ToList(); and InfrStadium class constructor: private InfrStadium(int teamId) { IList<Sector> teamSectors = (from sector in DbContext.sectors where sector.TeamId==teamId select sector) .ToList<>(); ... work with data } Current implementation perform 1+n queries, where n - number of records fetched the 1st time. I want to optimize that. And another one I would love to do using 'group' operator in way like this: IList<InfrStadium> stadiums = (from sector in DbContext.sectors group sector by sector.TeamId into team_sectors select new InfrStadium(team_sectors.Key, team_sectors) ).ToList(); with appropriate constructor: private InfrStadium(int iTeamId, IEnumerable<InfrStadiumSector> eSectors) { IList<Sector> teamSectors = eSectors.ToList(); ... work with data } But attempt to launch query causes the following error: Expression of type 'System.Int32' cannot be used for constructor parameter of type 'System.Collections.Generic.IEnumerable`1[InfrStadiumSector]' Question 1: Could you please explain, what is wrong here, I don't understand why 'team_sectors' is applied as 'System.Int32'? I've tried to change query a little (replace IEnumerable with IQueryeable): IList<InfrStadium> stadiums = (from sector in DbContext.sectors group sector by sector.TeamId into team_sectors select new InfrStadium(team_sectors.Key, team_sectors.AsQueryable()) ).ToList(); with appropriate constructor: private InfrStadium(int iTeamId, IQueryeable<InfrStadiumSector> eSectors) { IList<Sector> teamSectors = eSectors.ToList(); ... work with data } In this case I've received another but similar error: Expression of type 'System.Int32' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable1[InfrStadiumSector]' of method 'System.Linq.IQueryable1[InfrStadiumSector] AsQueryableInfrStadiumSector' Question 2: Actually, the same question: can't understand at all what is going on here... P.S. I have another to optimize query idea (describe here: Linq2Sql: query optimisation) but I would love to find a solution with 1 request to DB).

    Read the article

  • mysql count rows and grop them by month

    - by user2661296
    I have a table called cc_calls and there I have many call records I want to count them and group them in months I have a timestamp called starttime and I can use that row to extract the month, also limit the count for 12 months the results should be like: Month Count January 768768 February 876786 March 987979 April 765765 May 898797 June 876876 July 786575 August 765765 September 689787 October 765879 November 897989 December 876876 Can anyone guide me or show me the mysql query that I need to get this result.

    Read the article

  • What is the equivalent of REGEXP_SUBSTR in mysql?

    - by KandadaBoggu
    I want to extract a word from a string column of a table. description =========================== abc order_id: 2 xxxx yyy aa mmm order_id: 3 nn kk yw Expected result set order_id =========================== 2 3 Table will at most have 100 rows, text length is ~256 char and column always has one order_id present. So performance is not an issue. In Oracle, I can use REGEXP_SUBSTR for this problem. How would I solve this in MySQL?

    Read the article

  • C# - Rollback SqlTransaction in catch block - Problem with object accessability

    - by Marks
    Hi there. I've got a problem, and all articles or examples i found seem to not care about it. I want to do some database actions in a transaction. What i want to do is very similar to most examples: using (SqlConnection Conn = new SqlConnection(_ConnectionString)) { try { Conn.Open(); SqlTransaction Trans = Conn.BeginTransaction(); using (SqlCommand Com = new SqlCommand(ComText, Conn)) { /* DB work */ } } catch (Exception Ex) { Trans.Rollback(); return -1; } } But the problem is, that the SqlTransaction Trans is declared inside the try block. So it is not accessable in the catch() block. Most examples just do Conn.Open() and Conn.BeginTransaction() before the try block. But i think thats a bit risky, since both can throw multiple exceptions. Am I wrong, or do most people just ignore this risk? Whats the best solution to be able to rollback, if an exception happens. Thanks in advance, Marks

    Read the article

  • What is the corrrect way to increment a field making up part of a composit key

    - by Tr1stan
    I have a bunch of tables whose primary key is made up of the foreign keys of other tables (Composite key). Therefore for example the attributes (as a very cut down version) might look like this: A[aPK, SomeFields] 1:M B[bPK, aFK, SomeFields] 1:M C[cPK, bFK, aFK, SomeFields] as data this could look like: A[aPK, SomeFields]: 1, Foo 2, Bar B[bPK, aFK, SomeFields]: 1, 1, FooData1 2, 1, FooData2 1, 2, BarData1 2, 2, BarData2 C[cPK, bFK, aFK, SomeFields]: 1, 1, 1, FooData1More 2, 1, 1, FooData1More 1, 2, 1, FooData2More 2, 2, 1, FooData2More 1, 1, 2, BarData1More 2, 1, 2, BarData1More 1, 2, 2, BarData2More 2, 2, 2, BarData2More I've got this running in a MSSQL DBMS and I'm looking for the best way to increment the left most column, in each table when a new tuple is added to it. I can't use the Auto Increment Identity Specification option as that has no idea that it is part of a composite key. I also don't want to use any aggregate function such as: MAX(field)+1 as this will have adverse affects with multiple users inputting data, rolling back etc. There might however be a nice trigger based option here, but I'm not sure. This must be a common issue so I'm hoping that someone has a lovely solution. As a side which may or may not affect the answer, I'm using Entity Framework 1.0 as my ORM, within a c# MVC application.

    Read the article

  • Using a comma seperated data in MYSQL "IN" clause

    - by Sashi Kant
    I have a column in one of my table where I store multiple ids seperated by comma's. Is there a way in which I can use this column's value in the "IN" clause of a query. The column(city) has values like 6,7,8,16,21,2 I need to use as Select * from table where e_ID in (Select city from locations where e_Id=?) I am satisfied with Crozin's answer, but I am open to suggestions, views and options. Feel free to share your views.

    Read the article

  • Getting a linq table to be dynamically sent to a method

    - by Damian Spaulding
    I have a procedure: var Edit = (from R in Linq.Products where R.ID == RecordID select R).Single(); That I would like to make "Linq.Products" Dynamic. Something like: protected void Page_Load(object sender, EventArgs e) { something(Linq.Products); } public void something(Object MyObject) { System.Data.Linq.Table<Product> Dynamic = (System.Data.Linq.Table<Product>)MyObject; var Edit = (from R in Dynamic where R.ID == RecordID select R).Single(); My problem is that I my "something" method will not be able to know what table has been sent to it. so the static line: System.Data.Linq.Table Dynamic = (System.Data.Linq.Table)MyObject; Would have to reflect somthing like: System.Data.Linq.Table Dynamic = (System.Data.Linq.Table)MyObject; With being a dynamic catch all variable so that Linq can just execute the code just like I hand coded it statically. I have been pulling my hair out with this one. Please help.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 622 623 624 625 626 627 628 629 630 631 632 633  | Next Page >