When I select a set of rows from a table using IN, e.g. SELECT x.y, x.z FROM x WHERE x.id IN (23, 55, 44, 12) is there an SQL trick to get them back in the order given in the IN set? So in the example, assuming x has rows with ids 23, 55, 44 and 12, those four rows would be returned in that order. ...
I'm working on a query with a varchar column called ALCOHOL_OZ_PER_WK. Part of the query includes: where e.ALCOHOL_OZ_PER_WK >= 14 and get the errors: Arithmetic overflow error converting varchar to data type numeric. and: Error converting data type varchar to numeric. Looking into the values actually stored in the column, the larg...
I want to use DBMS_OUTPUT.PUT_LINE, but the number of rows exceeds just 1. There is nothing in the table which increments sequentially, so I can't use a loop. Is there some way to print out each row in the table? ...
Hi, I want to learn programming in SQL from some SQL tutorial sites of which I heard of here but I need some environment for executing query's. I think I have both SQL CE and SQL EE installed on my computer but I have some doubts about these DBMS and I don't know exactly how to use SQLCMD utility so I hope someone here will have time an...
I currently have a user's table which contains a one-to-one relationship for Youtube OAuth tokens. However, I now want to support multiple video sites and want to break this into a one-to-many relationship. I have setup the new tables: tokens - cols: id, site, username (the user's username on Youtube), oauth_token, oauth_secret ...
I have a table on which i want to perform some operations after every hour. For this I created a Stored Procedure but dont know how to call it after every hour. I know their are some kind of scheduled jobs but how to use them. Is it their some kind of service also that keeps on running continously every second where i can place my piec...
I asked a similar question before only to later discover that what I thought was the answer didn't work because I hadn't asked the right question (and hadn't noticed that the answerer told me this). To revisit, I have a table of comparisons. I'm trying to select the row with the maximum version for each set of two students. So what I had...
How can you write the following in MYSQL? SELECT AVG(col1) FROM table WHERE DISTINCT col2 more info: table col1 | col2 ----------- 2 | 555.555.555.555 5 | 555.555.555.555 4 | 444.444.444.444 returns '3' Basically I'm trying to select average value of col1 where ip addresses in col2 are distinct. ...
i have a table of songs and their beats-per-minute values, and i would like to build playlists that follow a curve like this: ^ . . . | . . b | . . p | . . m | . . x--------------------> time i realize this is probably not possible in a single SQL statement, but i'm in...
I'm currently involved in a very large supply chain management software system internal to where I'm employed. The system's UI is currently only implemented through ASP.NET, but we're in development of Windows Forms and Windows Mobile Compact interfaces as well. We have a pretty good setup in terms of separating the interface, business, ...
See http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Under the heading "Aggregate Functions in a Nested Set" I'm trying to work out a query similar to the example given, except I want it to work at a sub-tree level, so if I queried for MP3 players I would get a result set like; |NAME |COUNT| -------------...
so i got this statement, which works fine: SELECT MAX(patient_history_date_bio) AS med_date, medication_name FROM biological WHERE patient_id = 12) GROUP BY medication_name but, I would like to have the corresponding medication_dose also. So I type this up SELECT MAX(patient_history_date_bio) AS med_date, medication_name, ...
I have a mysql table with year (YEAR(4)) and month (TINYINT) columns. What is the best way to select all records in a range, e.g. 2009-10 .. 2010-02 ? ...
I need to analyze tens of thousands of lines of data. The data is imported from a text file. Each line of data has eight variables. Currently, I use a class to define the data structure. As I read through the text file, I store each line object in a generic list, List. I am wondering if I should switch to using a relational database (SQ...
I'm faced with the following and I'm not sure what's best practice. Consider the following table (which will get large): id PK | giver_id FK | recipient_id FK | date I'm using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, I'll also be doing lots of queries where I need to...
I am using adodb with php. I need to insert html into my database, and need to know the best method to escape the quotes before inserting it into the database? I tried using pg_escape_string() but it still does not seem to insert. What is the best method to do this? ...
I have the following SQL table: A|B --- w|x x|w y|z z|y Can I construct a query which will yield the following result: A|B --- w|x y|z To summarize, I would like to treat the two columns as an unordered set, such that (a,b) == (b,a). ...
Hello. How do i make a mysql query for more tables to check at once? i mean, something like: $sql = mysql_query("SELECT username FROM table1, table2, table3 WHERE username = '$username'"); $numer = mysql_num_rows($sql); echo "You have ".$number; can you do like this? What i want to do is to show a user all his posts from the whole s...
<?php $sql = mysql_query(" SELECT navn FROM member_film WHERE username = '$pusername' UNION SELECT meetup FROM member_meetups WHERE byusername = '$pusername' UNION SELECT title FROM member_tutorials WHERE username = '$pusername'"); $rowit = mysql_fetch_array($sql); $number = mysql_num_rows($sql); ?> <? echo $number; ?> <? echo $rowit["n...
Hi I am working on my assignment. It has 8 tables. Each table has a primary key. What do i do to generate a foreign key to a table? My reason for asking is that when I generate a primary key, a key symbol appears on the left. What do i do to make something a foreign key? ...