Search Results

Search found 27342 results on 1094 pages for 'sql denali'.

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 617/1094 | < Previous Page | 613 614 615 616 617 618 619 620 621 622 623 624  | Next Page >

  • Adding a computed column to an ActiveRecord query

    - by bmwbzz
    Hi, I am running a query using a scope and some conditions. Something like this: conditions[:offset] = (options[:page].to_i - 1) * PAGE_SIZE unless options[:page].blank? conditions[:limit] = options[:limit] ||= PAGE_SIZE scope = Promo.enabled.active results = scope.all conditions I'd like to add a computed column to the query (at the point when I'm now calling scope.all). Something like this: (ACOS(least(1,COS(0.71106459055501)*COS(-1.2915436464758)*COS(RADIANS(addresses.lat))*COS(RADIANS(addresses.lng))+ COS(0.71106459055501)*SIN(-1.2915436464758)*COS(RADIANS(addresses.lat))*SIN(RADIANS(addresses.lng))+ SIN(0.71106459055501)*SIN(RADIANS(addresses.lat))))*3963.19) as accurate_distance Is there a way to do that without just using find_by_sql and rewriting the whole existing query? Thanks!

    Read the article

  • Help with a MySQL SELECT WHERE Clause

    - by Dr. DOT
    A column in my table contains email addresses. I have a text string that contains the a few usernames of email addresses separated by commas. I can make text sting into an array if necessary to get my SELECT WHERE clause to work correctly. Text string search argument is 'bob,sally,steve' I want to produce a WHERE clause that only returns rows where the username portion of the email address in the table matches one of the usernames in my text string search argument. Thus a row with [email protected] would not be returned but [email protected] would be. Does anyone have a WHERE clause sample that produces this result? Thanks.

    Read the article

  • How can I use multiple Datatables on my CrystalReport?

    - by Sergio Tapia
    I have a dataset that connects with three databases. How can I attach my Crystalreport viewer so all three are included? protected void Page_Load(object sender, EventArgs e) { ReportDocument X = new ReportDocument(); DataTable DTable = new DataTable(); DataSet1TableAdapters.TableAdapterManager ????? = new WebApplication1.DataSet1TableAdapters.TableAdapterManager(); DTable = ???????? string ubicacion = Server.MapPath("crystalReport1.rpt"); X.Load(ubicacion); X.SetDataSource(DTable); CrystalReportViewer1.ReportSource = X; }

    Read the article

  • How to map combinations of things to a relational database?

    - by Space_C0wb0y
    I have a table whose records represent certain objects. For the sake of simplicity I am going to assume that the table only has one row, and that is the unique ObjectId. Now I need a way to store combinations of objects from that table. The combinations have to be unique, but can be of arbitrary length. For example, if I have the ObjectIds 1,2,3,4 I want to store the following combinations: {1,2}, {1,3,4}, {2,4}, {1,2,3,4} The ordering is not necessary. My current implementation is to have a table Combinations that maps ObjectIds to CombinationIds. So every combination receives a unique Id: ObjectId | CombinationId ------------------------ 1 | 1 2 | 1 1 | 2 3 | 2 4 | 2 This is the mapping for the first two combinations of the example above. The problem is, that the query for finding the CombinationId of a specific Combination seems to be very complex. The two main usage scenarios for this table will be to iterate over all combinations, and the retrieve a specific combination. The table will be created once and never be updated. I am using SQLite through JDBC. Is there any simpler way or a best practice to implement such a mapping?

    Read the article

  • Fact table with multiple facts

    - by Jeff Meatball Yang
    I have a dimension (SiteItem) has two important facts: perUserClicks perBrowserClicks however, within this dimension, I have groups of dimensions based on an attribute column (let's call the groups AboveFoldItems, LeftNavItems, OnTheFlyItems, etc.) each have more facts that are specific to that group: AboveFoldItems: eyeTime, loadTime LeftNavItems: mouseOverTime OnTheFlyItems: doesn't have any extra, but may in the future Is the following fact table schema ok? DateKey SessionKey SiteItemKey perUserClicks perBrowserClicks eyeTime loadTime mouseOverTime It seems a little wasteful since only some columns pertain to some dimension keys (the irrelevant facts are left NULL). But... this seems like it would be a common problem, so there should be a common solution for this, right?

    Read the article

  • How can I perform this query between related tables without using UNION?

    - by jeremy
    Suppose I have two separate tables that I watch to query. Both of these tables has a relation with a third table. How can I query both tables with a single, non UNION based query? I want the result of the search to rank the results by comparing a field on each table. Here's a theoretical example. I have a User table. That User can have both CDs and books. I want to find all of that user's books and CDs with a single query matching a string ("awesome" in this example). A UNION based query might look like this: SELECT "book" AS model, name, ranking FROM book WHERE name LIKE 'Awesome%' UNION SELECT "cd" AS model, name, ranking FROM cd WHERE name LIKE 'Awesome%' ORDER BY ranking DESC How can I perform a query like this without the UNION? If I do a simple left join from User to Books and CDs, we end up with a total number of results equal to the number of matching cds timse the number of matching books. Is there a GROUP BY or some other way of writing the query to fix this?

    Read the article

  • writing an Dynamic query in sqlserver

    - by prince23
    hi, DECLARE @sqlCommand varchar(1000) DECLARE @columnList varchar(75) DECLARE @city varchar(75) DECLARE @region varchar(75) SET @columnList = 'first_name, last_name, city' SET @city = '''London''' SET @region = '''South''' SET @sqlCommand = 'SELECT ' + @columnList + ' FROM dbo.employee WHERE City = ' + @city and 'region = '+@region --and 'region = '+@region print(@sqlCommand) EXEC (@sqlCommand) when i run this command i get an error Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'and'. and help would great thank you

    Read the article

  • Checking for reciprocal relationships in mysql. A trivial one table problem.

    - by calumbrodie
    I have a mysql table that stores relationships. Items can be related to another item in one direction, or both items can be related to each other. I want to return all items related to my primary item - but I also want to check to see if the related item has a 'reverse relationship' to the current item and show this as a boolean |--------------|---------------| | SKU | related_SKU | |--------------|---------------| | 0001 | 0099 | | 0002 | 0099 | | 0099 | 0001 | |--------------|---------------| If I want to get all relationships for SKU=0001 SELECT related_SKU from relationships where SKU='0001' returns |--------------| | related_SKU | |--------------| | 0099 | |--------------| but what I want is |--------------|---------------| | related_SKU | reciprocal | |--------------|---------------| | 0099 | 1 | |--------------|---------------| or SELECT related_SKU from relationships where SKU='0002' |--------------|---------------| | related_SKU | reciprocal | |--------------|---------------| | 0099 | 0 | |--------------|---------------| What's the best way to do this?

    Read the article

  • Detecting abuse for post rating system

    - by Steven smethurst
    I am using a wordpress plugin called "GD Star Rating" to allow my users to vote on stories that I post to one of my websites. http://everydayfiction.com/ Recently we have been having a lot of abuse of the system. Stories that have obviously been voted up artificially. "GD Star Rating" creates some detailed logs when a user votes on a story. Including; IP, Time of vote, and user_adgent, ect.. For example this story has 181 votes with an average of 5.7 http://www.everydayfiction.com/snowman-by-shaun-simon/ Most other stories only get around ~40 votes each day. At first I thought that the story got on to a social bookmarking site Digg, Stumbleupon ect... but after checking the logs I found that this story is getting the same amount of traffic that a normal story gets ~2k-3k. I checked if all the votes for this perpendicular story where coming from a the same IP address. I could see this happening if a user was at a school's computer lab using all their lab computers to vote up this story. Not one duplicate IP address in the log for this story. SELECT ip, COUNT(*) as count FROM wp_gdsr_votes_log WHERE id=3932 GROUP BY (ip ) ORDER BY count DESC Next I thought that a use might be using a proxy to vote up a story. I checked this by grouping all the browser user_agent together to see if there a single browser voting in a perpendicular way. At most 7 users where using a similar browser but voted sporadically (1-5), no evidence of wrong doing. SELECT user_agent, COUNT(*) as count FROM wp_gdsr_votes_log WHERE id=3932 GROUP BY ( user_agent) ORDER BY count DESC I check was to see if all the votes came in at a once. Maybe someone has a really interesting bot that can change the user_adgent and uses proxies, ect... At most 5 votes came with in 2 mins of each other. It doesn't seem to be any regularity on how people vote (IE a 5 vote does not come in once a min) SELECT * FROM wp_gdsr_votes_log WHERE id =3932 AND vote=5 ORDER BY wp_gdsr_votes_log.voted DESC The obvious solution to this problem is to force people to login before they are allowed to vote. But I would prefer to not have to go down that route unless it is absolutely necessary. I'm looking for suggestions on things to test for to detect the abuse.

    Read the article

  • Detecting changes between rows with same ID

    - by Noah
    I have a table containing some names and their associated ID, along with a snapshot: snapshot, id, name I need to identify when a name has changed for an id between snapshots. For example, in the following data: 1, 0, 'MOUSE_SPEED' 1, 1, 'MOUSE_POS' 1, 2, 'KEYBOARD_STATE' 2, 0, 'MOUSE_BUTTONS' 2, 1, 'MOUSE_POS' 2, 2, 'KEYBOARD_STATE' ...the meaning of id 0 changed with snapshot 2, but the others remained the same. I'd like to construct a query that (ideally) returns: 1, 0, 'MOUSE_SPEED' 2, 0, 'MOUSE_BUTTONS' I am using PostgreSQL v8.4.2.

    Read the article

  • MySQL: Count occurrences of known (or enumerated) distinct values

    - by Eilidh
    After looking at how to count the occurrences of distinct values in a field, I am wondering how to count the occurrences of each distinct value if the distinct values are known (or enumerated). For example, if I have a simple table - TrafficLight Colour ------------ ------ 1 Red 2 Amber 3 Red 4 Red 5 Green 6 Green where one column (in this case Colour) has known (or enumerated) distinct values, how could I return the count for each colour as a separate value, rather than as an array, as in the linked example. To return an array with a count of each colour (using the same method as in the linked example), the query would be something like SELECT Colour COUNT(*) AS ColourCount FROM TrafficLights GROUP BY Colour, and return an array - Colour ColourCount ------ ----------- Red 3 Amber 1 Green 2 What I would like to do is to return the count for each Colour AS a separate total (e.g. RedCount). How can I do this?

    Read the article

  • Multiple LIKE in SQL

    - by ninumedia
    I wanted to search through multiple rows and obtain the row that contains a particular item. The table in mySQL is setup so each id has a unique list (comma-delimited) of values per row. Ex: id | order 1 | 1,3,8,19,34,2,38 2 | 4,7,2,190,38 Now if I wanted to pull the row that contained just the number 19 how would I go about doing this? The possibilities I could figure in the list with a LIKE condition would be: 19, ,19 ,19, I tried the following and I cannot obtain any results, Thank you for your help! SELECT * FROM categories WHERE order LIKE '19,%' OR '%,19%' OR '%,19%' LIMIT 0 , 30

    Read the article

  • What's the most efficient query?

    - by Aaron Carlino
    I have a table named Projects that has the following relationships: has many Contributions has many Payments In my result set, I need the following aggregate values: Number of unique contributors (DonorID on the Contribution table) Total contributed (SUM of Amount on Contribution table) Total paid (SUM of PaymentAmount on Payment table) Because there are so many aggregate functions and multiple joins, it gets messy do use standard aggregate functions the the GROUP BY clause. I also need the ability to sort and filter these fields. So I've come up with two options: Using subqueries: SELECT Project.ID AS PROJECT_ID, (SELECT SUM(PaymentAmount) FROM Payment WHERE ProjectID = PROJECT_ID) AS TotalPaidBack, (SELECT COUNT(DISTINCT DonorID) FROM Contribution WHERE RecipientID = PROJECT_ID) AS ContributorCount, (SELECT SUM(Amount) FROM Contribution WHERE RecipientID = PROJECT_ID) AS TotalReceived FROM Project; Using a temporary table: DROP TABLE IF EXISTS Project_Temp; CREATE TEMPORARY TABLE Project_Temp (project_id INT NOT NULL, total_payments INT, total_donors INT, total_received INT, PRIMARY KEY(project_id)) ENGINE=MEMORY; INSERT INTO Project_Temp (project_id,total_payments) SELECT `Project`.ID, IFNULL(SUM(PaymentAmount),0) FROM `Project` LEFT JOIN `Payment` ON ProjectID = `Project`.ID GROUP BY 1; INSERT INTO Project_Temp (project_id,total_donors,total_received) SELECT `Project`.ID, IFNULL(COUNT(DISTINCT DonorID),0), IFNULL(SUM(Amount),0) FROM `Project` LEFT JOIN `Contribution` ON RecipientID = `Project`.ID GROUP BY 1 ON DUPLICATE KEY UPDATE total_donors = VALUES(total_donors), total_received = VALUES(total_received); SELECT * FROM Project_Temp; Tests for both are pretty comparable, in the 0.7 - 0.8 seconds range with 1,000 rows. But I'm really concerned about scalability, and I don't want to have to re-engineer everything as my tables grow. What's the best approach?

    Read the article

  • i need a query to retrieve the following constraint

    - by ANITHA
    I have the following tables and fields: +------------------+ +-------------------+ +---------------+ | Request | | RequestItem | | Item | +------------------+ +-------------------+ +---------------+ | + Requester_Name | | + Request_No | | + Item | +------------------+ +-------------------+ +---------------+ | + Request_No | | + Item | +------------------+ +-------------------+ I would like to filter the items which are selected under a particular request number, along with a specific requester name. How might I go about doing this?

    Read the article

  • Can I join two tables whereby the joined table is sorted by a certain column?

    - by Ferdy
    I'm not much of a database guru so I need some help on a query I'm working on. In my photo community project I want to richly visualize tags by not only showing the tag name and counter (# of images inside them), I also want to show a thumb of the most popular image inside the tag (most karma). The table setup is as follow: Image table holds basic image metadata, important is the karma field Imagefile table holds multiple entries per image, one for each format Tag table holds tag definitions Tag_map table maps tags to images In my usual trial and error query authoring I have come this far: SELECT * FROM (SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt FROM tag INNER JOIN tag_map ON (tag.id = tag_map.tag_id) INNER JOIN image ON tag_map.image_id = image.id INNER JOIN imagefile on image.id = imagefile.image_id WHERE imagefile.type = 'smallthumb' GROUP BY tag.name ORDER BY cnt DESC) as T1 WHERE cnt > 0 ORDER BY cnt DESC [column clause of inner query snipped for the sake of simplicity] This query gives me somewhat what I need. The outer query makes sure that only tags are returned for which there is at least 1 image. The inner query returns the tag details, such as its name, count (# of images) and the thumb. In addition, I can sort the inner query as I want (by most images, alphabetically, most recent, etc) So far so good. The problem however is that this query does not match the most popular image (most karma) of the tag, it seems to always take the most recent one in the tag. How can I make sure that the most popular image is matched with the tag?

    Read the article

  • Generating Running Sum of Ratings in SQL

    - by Koobz
    I have a rating table. It boils down to: rating_value created +2 april 3rd -5 april 20th So, every time someone gets rated, I track that rating event in the database. I want to generate a rating history/time graph where the rating is the sum of all ratings up to that point in time on a graph. I.E. A person's rating on April 5th might be select sum(rating_value) from ratings where created <= april 5th The only problem with this approach is I have to run this day by day across the interval I'm interested in. Is there some trick to generating a running total using this sort of data? Otherwise, I'm thinking the best approach is to create a denormalized "rating history" table alongside the individual ratings.

    Read the article

  • Selecting all but one field?

    - by gsquare567
    instead of SELECT * FROM mytable, i would like to select all fields EXCEPT one (namely, the 'serialized' field, which stores a serialized object). this is because i think that losing that field will speed up my query by a lot. however, i have so many fields and am quite the lazy guy. is there a way to say... `SELECT ALL_ROWS_EXCEPT(serialized) FROM mytable` ? thanks!

    Read the article

  • Updating records in Postgres using FROM clause

    - by Summer
    Hi, I'm changing my db schema, and moving column 'seat' from old_table to new_table. First I added a 'seat' column to new_table. Now I'm trying to populate the column with the values from old_table. UPDATE new_table SET seat = seat FROM old_table WHERE old_table.id = new_table.ot_id; This returns ERROR: column reference "seat" is ambiguous. UPDATE new_table nt SET nt.seat = ot.seat FROM old_table ot WHERE ot.id = nt.ot_id; Returns ERROR: column "nt" of relation "new_table" does not exist Ideas?

    Read the article

  • TextBox value not updated

    - by Jignesh
    I am fetching data from database to textbox using Linq.When i try update the same textbox value,it does not work. DAL.TournamentsDataContext tdc = new SchoolSports.DAL.TournamentsDataContext(); var tournamentTable = tdc.GetTable<DAL.Tournament>(); var tournamentRecord = (from rec in tournamentTable where rec.TournamentId == TournamentId select rec).Single(); tournamentRecord.Tournament_type = Tournament_type; tournamentRecord.Tournament_Name = Tournament_Name; ; tournamentRecord.Tournament_Level = Tournament_Level; tournamentRecord.Tournament_For = Tournament_For; tournamentRecord.Country_Code = Country_Code; tournamentRecord.Tournament_Status = Tournament_Status; tournamentRecord.Tournament_begin_date = Tournament_begin_date; tournamentRecord.Tournament_end_date = Tournament_end_date; tournamentRecord.Sponsored_By = Sponsored_By; tournamentRecord.Tournament_Details = Tournament_Details; var organiserTable = tdc.GetTable<DAL.Organiser>(); var organiserRecord = (from rec in organiserTable where rec.Tournament_Id == TournamentId select rec).Single(); organiserRecord.Name_Of_Organiser = OrName; organiserRecord.Telephone = OrTeleNo; organiserRecord.Email = OrEmail; organiserRecord.Mobile = OrMobile; organiserRecord.Fax = OrFax; if (Tournament_For == "School") { var invitedSchoolIdTable = tdc.GetTable<DAL.Invited_School>(); var invitedSchoolIdRecord = (from rec in invitedSchoolIdTable where rec.Tournament_Id == TournamentId select rec).Single(); invitedSchoolIdRecord.School_Ids = SchoolUniIds; } if (Tournament_For == "University") { var invitedUniversityTable = tdc.GetTable<DAL.Invited_University>(); var invitedUniversityIdRecord = (from rec in invitedUniversityTable where rec.Tournament_Id == TournamentId select rec).Single(); invitedUniversityIdRecord.University_Ids = SchoolUniIds; } tdc.SubmitChanges();

    Read the article

  • Extract multiple values from one column in MySql

    - by Neil
    I've noticed that MySql has an extensive search capacity, allowing both wildcards and regular expressions. However, I'm in somewhat in a bind since I'm trying to extract multiple values from a single string in my select query. For example, if I had the text "<span>Test</span> this <span>query</span>", perhaps using regular expressions I could find and extract values "Test" or "query", but in my case, I have potentially n such strings to extract. And since I can't define n columns in my select statement, that means I'm stuck. Is there anyway I could have a list of values (ideally separated by commas) of any text contained with span tags? In other words, if I ran this query, I would get "Test,query" as the value of spanlist: select <insert logic here> as spanlist from HtmlPages ...

    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 | 613 614 615 616 617 618 619 620 621 622 623 624  | Next Page >