Search Results

Search found 27472 results on 1099 pages for 'sql humor'.

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 614/1099 | < Previous Page | 610 611 612 613 614 615 616 617 618 619 620 621  | Next Page >

  • 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

  • oracle plsql select pivot without dynamic sql to group by

    - by kayhan yüksel
    To whom it may respond to, We would like to use SELECT function with PIVOT option at a 11g r2 Oracle DBMS. Our query is like : "select * from (SELECT o.ship_to_customer_no, ol.item_no,ol.amount FROM t_order o, t_order_line ol WHERE o.NO = ol.order_no and ol.item_no in (select distinct(item_no) from t_order_line)) pivot --xml ( SUM(amount) FOR item_no IN ( select distinct(item_no) as item_no_ from t_order_line));" As can be seen, XML is commented out, if run as PIVOT XML it gives the correct output in XML format, but we are required to get the data as unformatted pivot data, but this sentence throws error : ORA-00936: missing expression Any resolutions or ideas would be welcomed, Best Regards -------------if we can get the result of this to sys_refcursor using execute immediate it will be solved ------------------------ the procedure : PROCEDURE pr_test2 (deneme OUT sys_refcursor) IS v_sql NVARCHAR2 (4000) := ''; TYPE v_items IS TABLE OF NVARCHAR2 (30); v_pivot_items NVARCHAR2 (4000) := ''; BEGIN FOR i IN (SELECT DISTINCT (item_no) AS items FROM t_order_line) LOOP v_pivot_items := ',''' || i.items || '''' || v_pivot_items; END LOOP; v_pivot_items := LTRIM (v_pivot_items, ','); v_sql := 'begin select * from (SELECT o.ship_to_customer_no, ol.item_no,ol.amount FROM t_order o, t_order_line ol WHERE o.NO = ol.order_no and OL.ITEM_NO in (select distinct(item_no) from t_order_line)) pivot --xml ( SUM(amount) FOR item_no IN (' || v_pivot_items || '));end;'; open DENEME for select v_sql from dual; Kayhan YÜKSEL

    Read the article

  • Computed column should result to string

    - by strakastroukas
    Here is a snap of my database. Both col1 and col2 are declared as int. My ComputedColumn currently adds the Columns 1 and 2, as follows... col1 col2 ComputedColumn 1 2 3 4 1 5 Instead of this, my ComputedColumn should join the columns 1 and 2 (includimg the '-' character in the middle) as follows... col1 col2 ComputedColumn 1 2 1-2 4 1 4-1 So, what is the correct syntax?

    Read the article

  • ASP.Net Gridview paging, pageindex always == 0.

    - by David Archer
    Hi all, Having a slight problem with my ASP.Net 3.5 app. I'm trying to get the program to pick up what page number has been clicked. I'm using ASP.Net's built in AllowPaging="True" function. It's never the same without code, so here it is: ASP.Net: <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="Vertical" Width="960px" AllowSorting="True" EnableSortingAndPagingCallbacks="True" AllowPaging="True" PageSize="25" > <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> C#: var fillTable = from ft in db.IncidentDatas where ft.pUserID == Convert.ToInt32(ClientList.SelectedValue.ToString()) select new { Reference = ft.pRef.ToString(), Date = ft.pIncidentDateTime.Value.Date.ToShortDateString(), Time = ft.pIncidentDateTime.Value.TimeOfDay, Premesis = ft.pPremises.ToString(), Latitude = ft.pLat.ToString(), Longitude = ft.pLong.ToString() }; if (fillTable.Count() > 0) { GridView1.DataSource = fillTable; GridView1.DataBind(); var IncidentDetails = fillTable.ToList(); for (int i = 0; i < IncidentDetails.Count(); i++) { int pageno = GridView1.PageIndex; int pagenostart = pageno * 25; if (i >= pagenostart && i < (pagenostart + 25)) { //Processing } } } Any idea why GridView1.PageIndex is always = 0? The thing is, the processing works correctly for the grid view.... it will always go to the correct paging page, but it's always 0 when I try to get the number. Help!

    Read the article

  • LINQtoSQL: Query to return List<String>

    - by ctrlShiftBryan
    I have a LINQ query that returns some object like this... var query = from c in db.Customers where ... select c; Then I do this List<String> list = new List<String>(); foreach (ProgramLanguage c in query) { //GetUL returns a String list.Add(GetUL(c.Property,c.Property2)); } Is there a way to combine into something list this? var query = from c in db.Customers where ... select new { GetUL(c.Property,c.Property2) }).ToList<String>();

    Read the article

  • Autmatically create table on MySQL server based on date?

    - by Anthony
    Is there an equivalent to cron for MySQL? I have a PHP script that queries a table based on the month and year, like: SELECT * FROM data_2010_1 What I have been doing until now is, every time the script executes it does a query for the table, and if it exists, does the work, if it doesn't it creates the table. I was wondering if I can just set something up on the MySQL server itself that will create the table (based on a default table) at the stroke of midnight on the first of the month. Update Based on the comments I've gotten, I'm thinking this isn't the best way to achieve my goal. So here's two more questions: If I have a table with thousands of rows added monthly, is this potentially a drag on resources? If so, what is the best way to partition this table, since the above is verboten? What are the potential problems with my home-grown method I originally thought up?

    Read the article

  • Precision of Interval for PL/SQL Function value

    - by Gary
    Generally, when you specify a function the scale/precision/size of the return datatype is undefined. For example, you say FUNCTION show_price RETURN NUMBER or FUNCTION show_name RETURN VARCHAR2. You are not allowed to have FUNCTION show_price RETURN NUMBER(10,2) or FUNCTION show_name RETURN VARCHAR2(20), and the function return value is unrestricted. This is documented functionality. Now, I get an precision error (ORA-01873) if I push 9999 hours (about 400 days) into the following. The limit is because the default days precision is 2 DECLARE v_int INTERVAL DAY (4) TO SECOND(0); FUNCTION hhmm_to_interval return INTERVAL DAY TO SECOND IS v_hhmm INTERVAL DAY (4) TO SECOND(0); BEGIN v_hhmm := to_dsinterval('PT9999H'); RETURN v_hhmm; -- END hhmm_to_interval; BEGIN v_int := hhmm_to_interval; end; / and it won't allow the precision to be specified directly as part of the datatype returned by the function. DECLARE v_int INTERVAL DAY (4) TO SECOND(0); FUNCTION hhmm_to_interval return INTERVAL DAY (4) TO SECOND IS v_hhmm INTERVAL DAY (4) TO SECOND(0); BEGIN v_hhmm := to_dsinterval('PT9999H'); RETURN v_hhmm; -- END hhmm_to_interval; BEGIN v_int := hhmm_to_interval; end; / I can use a SUBTYPE DECLARE subtype t_int is INTERVAL DAY (4) TO SECOND(0); v_int INTERVAL DAY (4) TO SECOND(0); FUNCTION hhmm_to_interval return t_int IS v_hhmm INTERVAL DAY (4) TO SECOND(0); BEGIN v_hhmm := to_dsinterval('PT9999H'); RETURN v_hhmm; -- END hhmm_to_interval; BEGIN v_int := hhmm_to_interval; end; / Any drawbacks to the subtype approach ? Any alternatives (eg some place to change a default precision) ? Working with 10gR2.

    Read the article

  • Thinking of an Inner Join as a Cross Join and then satisfying some condition(s)?

    - by Jian Lin
    It seems like the safest way to think of an Inner Join is to think of it as a Cross Join and then satisfying some condition(s)? Because the equi-join can be obvious, but the non-equi-join can be a bit confusing. But if we always use the Cross Join, and then filter out the ones satisfying the condition, then we get the resulting table. In other words, we can always analyze it by using the first record on the left table, and then go through every single records on the right, and then repeat that for 2nd record on the left, and for the 3rd, 4th, ... etc. So in our mind, we can analyze it using this way, and it is like O(n^2), although what happens in the DBMS maybe that it is a lot faster (when an index is present). Is there another good way to think of it besides this method?

    Read the article

  • LINQ Query returns false when it should be true.

    - by deliriousDev
    I have the following LINQ query written by a former developer and it isn't working when it should. public bool IsAvailable(Appointment appointment) { var appointments = _appointmentRepository.Get; var shifts = _scheduleRepository.Get; var city = _customerRepository.Find(appointment.CustomerId).City ?? appointment.Customer.City; const int durationHour = 1; DateTime scheduledEndDate = appointment.ScheduledTime.Add(new TimeSpan(durationHour, 0, 0)); var inWorkingHours = shifts .Where(x => //Check if any available working hours x.Employee.City == city && x.ShiftStart <= appointment.ScheduledTime && x.ShiftEnd >= scheduledEndDate && //check if not booked yet !appointments .Where(a => (appointment.Id == 0 || a.Id != appointment.Id) && a.Employee.Id == x.Employee.Id && ( (a.ScheduledTime <= appointment.ScheduledTime && appointment.ScheduledTime <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) || (a.ScheduledTime <= scheduledEndDate && scheduledEndDate <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) )) .Select(a => a.Employee.Id) .Contains(x.Employee.Id) ); if (inWorkingHours.Any()) { var assignedEmployee = inWorkingHours.FirstOrDefault().Employee; appointment.EmployeeId = assignedEmployee.Id; appointment.Employee = assignedEmployee; return true; } return false; } The query is suppose to handle the following scenarios Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time But Does not match any employees in same city - (Return true, Assign as "Unassigned") Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time AND Employee for that shift is in the same city as the customer (Return True AND Assign to the employee) If the customer is NOT in the same city as an employee we assign the appointment as "Unassigned" as along as the scheduledTime is within an of the employees shift start/end times If the customer is in the same city as an employee we assign the appointment to one of the employees (firstOrdefault) and occupy that timeslot. Appointments CAN NOT overlap (Assigned Ones). Unassigned can't overlap each other. This query use to work (I've been told). But now it doesn't and I have tried refactoring it and various other paths with no luck. I am now on week two and just don't know where the issue in the query is or how to write it. Let me know if I need to post anything further. I have verified appointments, shifts, city all populate with valid data so the issue doesn't appear to be with null or missing data.

    Read the article

  • Primary key/foreign Key naming convention

    - by Jeremy
    In our dev group we have a raging debate regarding the naming convention for Primary and Foreign Keys. There's basically two schools of thought in our group: 1) Primary Table (Employee) Primary Key is called ID Foreign table (Event) Foreign key is called EmployeeID 2) Primary Table (Employee) Primary Key is called EmployeeID Foreign table (Event) Foreign key is called EmployeeID I prefer not to duplicate the name of the table in any of the columns (So I prefer option 1 above). Conceptually, it is consisted with a lot of the recommended practices in other languages, where you don't use the name of the object in its property names. I think that naming the foreign key EmployeeID (or Employee_ID might be better) tells the reader that it is the ID column of the Employee Table. Some others prefer option 2 where you name the primary key prefixed with the table name so that the column name is the same throughout the database. I see that point, but you now can not visually distinguish a primary key from a foreign key. Also, I think it's redundant to have the table name in the column name, because if you think of the table as an entity and a column as a property or attribute of that entity, you think of it as the ID attribute of the Employee, not the EmployeeID attribute of an employee. I don't go an ask my coworker what his PersonAge or PersonGender is. I ask him what his Age is. So like I said, it's a raging debate and we go on and on and on about it. I'm interested to get some new perspective.

    Read the article

  • stored procedure to find value in 2 columns out of 3

    - by user1510533
    I am putting in the samle date and i am supposed to do something similar what i am asking. I want to run a query that would pull values in any two columns out 3 if it has a 1 or if any one column has a 1 it will return just those results. However it should search all three columns and in any of the three columns where it found value as 1 it should return that result. Can anyone please help me with this. Thanks in advance. ID Patient Patient Name prio prio2 prio3 ------------------------------------------------- 1 101563 Robert Riley 1 1 1 2 101583 Cody Ayers 1 0 1 3 101825 Jason Lawler 0 0 1 4 101984 Dustin Lumis 1 0 0 5 102365 Stacy smith 1 0 0 6 102564 Frank Milon 1 0 0 7 102692 Thomas Kroning 1 0 0 8 102856 Andrew Philips 1 0 0 9 102915 Alice Davies 0 0 1 10 103785 Jon Durley 0 0 1 11 103958 Clayton Folsom 1 1 1 12 104696 Michelle Holsley 1 1 1 13 104983 Teresa Jones 1 0 1 14 105892 Betsy Prat 1 1 0 15 106859 Casey Ayers 1 1 0

    Read the article

  • Little Employee/Shift timetable HELP!!!

    - by DAVID
    Morning Guys, I have the following tables: operator(ope_id, ope_name) ope_shift(ope_id, shift_id, shift_date) shift(shift_id, shift_start, shift_end) here is a better view of the data http://latinunit.net/emp_shift.txt here is the screenshot of a select statement to the tables http://img256.imageshack.us/img256/4013/opeshift.jpg im using this code SELECT OPE_ID, COUNT(OPE_ID) AS Total_shifts from operator_shift group by ope_id; to view the current total shifts per operator and it works, BUT if there was 500 more rows it would count them all aswell, THE QUESTION is, anyone has a better way of making my database work, or how can i tell the system that those rows are a whole month, i remember i friend said something about count then devide by 30 but im not sure, what if the month isnt finished? and you want to show the emp with highest shifts to date

    Read the article

  • How to avoid timestamp issue in a long query?

    - by pingi
    Hi, I have the following 2 tables: items: id int primary key bla text events: id_items int num int when timestamp without time zone ble text composite primary key: id_items, num and want to select to each item the most recent event (the newest 'when'). I wrote an request, but I don't know if it could be written more efficiently. Also on PostgreSQL there is a issue with comparing Timestamp objects: 2010-05-08T10:00:00.123 == 2010-05-08T10:00:00.321 so I select with 'MAX(num)' Any thoughts how to make it better? Thanks. SELECT i.*, ea.* FROM items AS i JOIN ( SELECT t.s AS t_s, t.c AS t_c, max(e.num) AS o FROM events AS e JOIN ( SELECT DISTINCT id_item AS s, MAX(when) AS c FROM events GROUP BY s ORDER BY c ) AS t ON t.s = e.id_item AND e.when = t.c GROUP BY t.s, t.c ) AS tt ON tt.t_s = i.id JOIN events AS ea ON ea.id_item = tt.t_s AND ea.cas = tt.t_c AND ea.num = tt.o;

    Read the article

  • Are GUID primary keys bad in theory, or just practice?

    - by Yarin
    Whenever I design a database I automatically start with an auto-generating GUID primary key for each of my tables (excepting look-up tables) I know I'll never lose sleep over duplicate keys, merging tables, etc. To me it just makes sense philosophically that any given record should be unique across all domains, and that that uniqueness should be represented in a consistent way from table to table. I realize it will never be the most performant option, but putting performance aside, I'd like to know if there are philosophical arguments against this practice?

    Read the article

  • How to stop looking in a database after X rows are found?

    - by morningface
    I have a query to a database that returns a number X of results. I am looking to return a maximum of 10 results. Is there a way to do this without using LIMIT 0,9? I'll use LIMIT if I have to, but I'd rather use something else that will literally stop the searching, rather than look at all rows and then only return the top 10.

    Read the article

  • Simple Database normalization question...

    - by user365531
    Hi all, I have a quick question regarding a database that I am designing and making sure it is normalized... I have a customer table, with a primary key of customerId. It has a StatusCode column that has a code which reflects the customers account status ie. 1 = Open, 2 = Closed, 3 = Suspended etc... Now I would like to have another field in the customer table that flags whether the account is allowed to be suspended or not... certain customers will be automatically suspended if they break there trading terms... others not... so the relevant table fields will be as so: Customers (CustomerId(PK):StatusCode:IsSuspensionAllowed) Now both fields are dependent on the primary key as you can not determine the status or whether suspensions are allowed on a particular customer unless you know the specific customer, except of course when the IsSuspensionAllowed field is set to YES, the the customer should never have a StatusCode of 3 (Suspended). It seems from the above table design it is possible for this to happen unless a check contraint is added to my table. I can't see how another table could be added to the relational design to enforce this though as it's only in the case where IsSuspensionAllowed is set to YES and StatusCode is set to 3 when the two have a dependence on each other. So after my long winded explanation my question is this: Is this a normalization problem and I'm not seeing a relational design that will enforce this... or is it actually just a business rule that should be enforced with a check contraint and the table is in fact still normalized. Cheers, Steve

    Read the article

  • Postgresql Altering Table

    - by Jahn
    Is it possible to alter a table to add a new column and make that column a foreign key to another table in a single command in Postgresql? "alter table x add column y id references z(id)" doesn't seem to work as I had hoped.

    Read the article

  • Problem with mysql query to replace a string

    - by alex
    I've used mysql's update replace function before, but even though I think I'm following the same syntax, I can't get this to work. Here's what I'm trying to do: UPDATE contained_widgets SET preference_values = REPLACE(preference_values, '<li><a_href="/enewsletter"><span class="not-tc">eNewsletter</span></a></li>', '<li><a_href="/enewsletter"><span class="not-tc">eNewsletter</span></a></li> <li> <a_href="/projects"><span class="not-tc">Projects</span></a></li>'); Query OK, 0 rows affected (0.00 sec) Rows matched: 77 Changed: 0 Warnings: 0 I don't see what I'm missing. Any help is appreciated. I edited "a " to "a_" because the site thinks I'm posting spam links otherwise.

    Read the article

  • SqlServer2008 + expensive union

    - by Tim Mahy
    Hi al, we have 5 tables over which we should query with user search input throughout a stored procedure. We do a union of the similar data inside a view. Because of this the view can not be materialized. We are not able to change these 5 tables drastically (like creating a 6th table that contains the similar data of the 5 tables and reference that new one from the 5 tables). The query is rather expensive / slow what are our other options? It's allowed to think outside the box. Unfortunately I cannot give more information like the table/view/SP definition because of customer confidentiality... greetings, Tim

    Read the article

  • Querying Two Tables At Once

    - by John
    Hello, I am trying to do what I believe is called a join query. First, in a MySQL table called "login," I want to look up what "loginid" is in the record where "username" equals $profile. (This will be just one record / row in the MySQL table). Then, I want to take that "loginid" and look up all rows / records in a different MySQL table called "submission," and pull data that have that "loginid." This could possibly be more than one record / row. How do I do this? The code below doesn't seem to work. Thanks in advance, John $profile = mysql_real_escape_string($_GET['profile']); $sqlStr = "SELECT l.username, l.loginid, s.loginid, s.submissionid, s.title, s.url, s.datesubmitted, s.displayurl FROM submission AS s, login AS l WHERE l.username = '$profile', s.loginid = l.loginid ORDER BY s.datesubmitted DESC";

    Read the article

  • How to save multiple column in database

    - by shamim
    I have a text file.I need to get data from this text file and show on grid , 1)After this user can update information from gridview, 2)Click on save button save data on database. Before clicking on button data don't save on database.How to do that?

    Read the article

  • How can i get rid of 'ORA-01489: result of string concatenation is too long' in this query?

    - by core_pro
    this query gets the dominating sets in a network. so for example given a network A<----->B B<----->C B<----->D C<----->E D<----->C D<----->E F<----->E it returns B,E B,F A,E but it doesn't work for large data because i'm using string methods in my result. i have been trying to remove the string methods and return a view or something but to no avail With t as (select 'A' as per1, 'B' as per2 from dual union all select 'B','C' from dual union all select 'B','D' from dual union all select 'C','B' from dual union all select 'C','E' from dual union all select 'D','C' from dual union all select 'D','E' from dual union all select 'E','C' from dual union all select 'E','D' from dual union all select 'F','E' from dual) ,t2 as (select distinct least(per1, per2) as per1, greatest(per1, per2) as per2 from t union select distinct greatest(per1, per2) as per1, least(per1, per2) as per1 from t) ,t3 as (select per1, per2, row_number() over (partition by per1 order by per2) as rn from t2) ,people as (select per, row_number() over (order by per) rn from (select distinct per1 as per from t union select distinct per2 from t) ) ,comb as (select sys_connect_by_path(per,',')||',' as p from people connect by rn > prior rn ) ,find as (select p, per2, count(*) over (partition by p) as cnt from ( select distinct comb.p, t3.per2 from comb, t3 where instr(comb.p, ','||t3.per1||',') > 0 or instr(comb.p, ','||t3.per2||',') > 0 ) ) ,rnk as (select p, rank() over (order by length(p)) as rnk from find where cnt = (select count(*) from people) order by rnk ) select distinct trim(',' from p) as p from rnk where rnk.rnk = 1`

    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 | 610 611 612 613 614 615 616 617 618 619 620 621  | Next Page >