May not have been the best title but anyways.. I have a stored procedure which until now had 1 optional parameter which I easily solved with 1 if statement. Now it has 2 which brings it up to 4 if statements. I would like to know if there is a better way to write a query then the following. Especially if/when a variable count goes up to...
I need to drop 175 of 180 tables in Access 2003 (using an Access 2000 format database). The first thing I tried was going to the table list and trying to select all, control-clicking the ones I want to keep, and choosing "Delete". However, it turns out you can only select one at a time. Then I found out a way to get a list of all the ...
Hi to all! I didn't know where to look or ask, so I thought that SO could be a good place to start. My girlfriend, who's studying economics, has a "informative systems" exams. Part of the exam is about the sql language. It's pretty basic stuff, only selects. She has a plethora of written test cases out of her textbook. I'm helping her ...
I have data in a mysql table in long / tall format (described below) and want to convert it to wide format. Can I do this using just sql? Easiest to explain with an example. Suppose you have information on (country, key, value) for M countries, N keys (e.g. keys can be income, political leader, area, continent, etc.) Long format has 3 ...
I would like to find all duplicate records by name in a customer table using MySQL including those that do not match exactly. I know I can use the query SELECT id, name FROM customer GROUP BY name HAVING count(*) > 1; to find all rows that match exactly, but I want to find all duplicate rows matching with a LIKE clause. For instance...
I have a temp table populated in a sproc that is similar to this: Company Col1 Col2 Col3 Total Company1 4 3 2 9 Company2 1 0 3 4 Total ? ? ? ? Is there a slick way to get the bottom total column populated with SUM of each row in one shot without h...
Table_1 D_ID Integer Deposit_amt integer Table_2 Total_ID Total_amt integer Is it possible to write a select statement to find all the rows in Table_1 whose Deposit_amt sum to the Total_amt in Table_2. There are multiple rows in both tables. Say the first row in Table_2 has a Total_amt=100. I would want to know that in...
For example, given this table of sparse ids: |id| | 1| | 2| | 3| | 6| | 7| I can obtain the highest "id" from my table using this query: SELECT max(id) FROM Comics I get: |id| | 7| How can I get the "id" just preceding the highest "id" (even if the values aren't continuous)? ...
So I have this SQL query, <named-query name="NQ::job_exists"> <query> select 0 from dual where exists (select * from job_queue); </query> </named-query> Which I plan to use like this: Query q = em.createNamedQuery("NQ::job_exists"); List<Integer> results = q.getResultList(); boolean exists = !results.isEmpty(); ...
Hello guys. My skills with SQL are simple, so i'm not sure how to do this.. Here it goes. Lets consider this. Table A ID Name LastNameID 1 Jonh 23 2 Helen 19 Table B ID LastName 23 Bauer 19 Summers . SELECT ID Name...
This may be a silly question, but it may shed some light on how joins work internally. Let's say I have a large table L and a small table S (100K rows vs. 100 rows). Would there be any difference in terms of speed between the following two options?: OPTION 1: OPTION 2: --------- --------- SELECT * ...
Hi I try to select all records of a table (Postgres DB) with the following sql: SELECT * FORM 'tablename' WHERE 'myTimestampRow' >= now() There's allways an error message, telling me that there's an 'invalid input syntax for type timestamp with time zone: "myTimestampRow"'. What's wrong with the above query? ...
I don't know if I've chosen the appropriate title for this question (if not, please change it accordingly) but consider the following simplified table structure I'm working with: ---------------------------------------------- | date | i | j | k | x | y | z | ---------------------------------------------- | 100209 | 1 | ...
I have a column which is a Timestamp. It records something like: 2010-02-08 12:10:22 Then I use this in php: $postdate = date( "j F", strtotime( $row['modify_date'] ) ); And it can output something like: 8 February My Q is, how can I change the date-text so it outputs the month name in another language (swedish specifically)? Ex:...
I have this to select a date from a mysql db, and compare it to an array of month-names in swedish language. $monthnames = array("","Januari","Februari","Mars","April","Maj","Juni","Juli","Augusti","September","Oktober","November","December"); $postdate = $monthnames[date("n", strtotime( $row['modify_date'] ))]; //Outputs something lik...
Hi, I am currently trying to ring more performance out of my reporting website which uses linq to sql and an sql server express 2008 database. I am finding that as I now approach a million rows in on of my more 'ugly' tables that performance is becoming a real issue, with one report in particular taking 3 minutes to generate. Essential...
What's the difference between SELECT * FROM `this` and SELECT * FROM this ? ...
Which of the following queries are correct SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME WHERE STATUS IN('OPEN','UPDATED') AND ITEM_TYPE IN ('ITEM1','ITEM2') AND CREATED_TIME BETWEEN 'XX' AND 'YY' AND UPDATED_TIME BETWEEN 'XX' AND 'ZZ' SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME WHERE STA...
I have a "games" table which contains player_id and player_action. player_id | player_action 1 | move 1 | move 1 | attack 2 | attack 2 | attack 3 | move 3 | move And I have to get all player with "move" action but only one row per player, result should looks like ...
In a table I've got 3 columns: id tag1 tag2 id is a primary key. And i only want one unique tag1-tag2-combination in that table. eg if one entry looks like: id: 1 tag1: cat tag2: dog I dont want a second entry like this one beneath to get inserted: id: 2 tag1: cat tag2: dog So i made all 3 columns primary keys but the problem...