sql

Stop MySQL Reusing AUTO_INCREMENT IDs

I have a table with an AUTO_INCREMENT primary key. If the last row in the table is deleted, the next-inserted row will take the same ID. Is there a way of getting MySQL to behave like t-SQL, and not reuse the ID? Then if the deleted row is erroneously referenced from something external to the database, no rows will be returned, highligh...

How do I get records before and after given one?

Thanks everyone for the feedback. This is a favorite. Hi All, Log Table Id, Message, Type 1, John Doe 2, Jane Smith 3, Error 4, Jane Smith Is there a way to get the error record and the surrounding records? Find all Errors and the record before and after them. Thanks, rod. ...

Sql Query to find Temp tables in DB

Please suggest me a query to find temp database tables. ...

Is it always a good practice to use aliases in sql joins or nested queries?

Is it always a best practice to use - Select E.Id,D.DeptName from Employee E join Dept D on E.DeptId=D.Id instead of - Select Employee.Id,Dept.DeptName from Employee join Dept on Employee.DeptId=Dept.Id Other than readability and reducing length of the query what are benefits of using aliases ? When I consulted with our Database ex...

merging rows from sql query result

What would it be the simplest way to merge a result like this from a sql query to be displayed in an asp.net gridview? NULL Tarde Fer W. Lunes Mañana NULL Fer W. Lunes I need the result to look like this Mañana Tarde Fer W. Lunes ...

Percentage row makes up of total where each row determine by group by clause

I'm sure this is a stupid n00b question, but I have the following results table (which is created through a long-winded query I only half understand) and what I'd like is to be able to add in two columns that work out the row percentage relative to the total: Sample results table: CREATE TABLE #temp (category varchar(30) ,count_people ...

Optimize sql query

Is it possable to optimize this query? SELECT count(locId) AS antal , locId FROM `geolitecity_block` WHERE (1835880985>= startIpNum AND 1835880985 <= endIpNum) OR (1836875969>= startIpNum AND 1836875969 <= endIpNum) OR (1836878754>= startIpNum AND 1836878754 <= endIpNum) ... ... OR (1843488110>= startIpNum AND 1843488110 ...

SQL convert int to time

I have a database that displays time as an integer. However I am wanting to output this into a report with the correct format. This is the format that I would like to change: eg. 183000 would become 18:30 500 would become 00:05 160000 would become 16:00 and so on. I have had a look and CAST and CONVERT but not succefully manage...

SQL Server 2005 history tables using triggers

I have a table and want to insert the current values of records that are being updated or deleted. I tried to create a trigger on before update and on before delete but SQL Server 2005 doesn't like the before word. How do I get this to work? I want the trigger to take the current record and copy it to a history table that is a mirror ...

SQL EXCEPT TOP 10 records in Desc order

I'm want pull all records except the TOP 10 in my statement but I keep running into problems when doing my ORDER BY in my subquery "Incorrect Syntax near the keyword ORDER" @ID INT as SELECT ComVID, VID, MID, Ucomment, UdateAdded, MemberId, UserName, Avatar FROM table.miComments JOIN table.mbrProfile2 ON MID = MemberId WHERE VID = @I...

SQL Server efficiently filter rows where times are not near another table's times

I have two tables and I'm looking for the rows in one table where a time column is not near any of the values in another table's time column. (Near is defined as within a minute). Here's a code sample: create table temp1 ( id int identity primary key, value datetime not null ) GO create index ix_temp1 on temp1(value, id); GO ...

oracle sleep or lock to check the processing

I just want to see whether the data is getting inserted on the table or not.. So i have written like this: select count(*) from emp; dbms_lock.sleep(1); select count(*) from emp; So that it will sleep for 1 min . Even after sleep if the 1st count and 2nd count are different then the data is getting inserted into the table. Otherwise...

SQL: Building where clause

Is there a way to get build a WHERE clause on the fly in a sql statement? This code is within a Stored Procedure. I have x amount of parameters and each parameter's default value is NULL SELECT * FROM MyTable m WHERE IF(NOT(@Param1 IS NULL)) m.Col1 = @Param1 END IF AND IF(NOT(@Param2 IS NULL)) m.Col2 = @Param2 END...

Force table to start ID increment from 0 again in ActiveRecord?

I am trying to setup seed data for my Rails app and am counting on the id of each model being a certain value. How can I tell the table to, after MyModel.destroy_all to start counting from 0 again instead of where it left off? ...

mysql query WHERE in one table but not the other

EDIT:This did it: SELECT DISTINCT profileid FROM profilesrelevation WHERE profileid NOT IN ( SELECT profileid FROM profiles ) I need to get the profileid values that exist in profilesrelevation table but not in profiles table profiles table have 8107 diffrent "profileid" values while profilesrelevation table has 8380 diffrent "...

what is the purpose of the 'right' value in a nested tree?

If I'm looking for nested nodes of a parent, I'd query for children like this: parent.left < child.left This is always true though: child.right < parent.right So why do all examples I've found run a between query? thanks, Matt ...

User Defined Function Best Practice

Hi All, I am contemplating using some user defined function calls within some of my queries instead of using a bunch of inline case statements. The inline statements will probably perform better, but the functions make it so much easier to view and possibly maintain. I just wanted to get an idea of what the typical best practice is fo...

GROUP BY query not ordering as expected

I don't know why I can't make this query work the way I want, I hope you can enlighten me. SELECT e.id, e.num, e.name, s.name as s_name, t.num as t_num, e.show_id FROM s_episodes e LEFT JOIN shows s ON s.id = e.show_id LEFT JOIN s_seasons t ON t.id = e.season_id GROUP BY...

MySQL JOIN help

Hello, I hate asking for code but I just can't seem to do the below - Staff | lastname - name - position | | Henderson | John | A | | Howard | Bob | B | | Hendry | Chris | B | Max_person | lastname - change | | Henderson | John | | Howard | Bob | | Hendry ...

Android SQLite memory leak.

I know this is a long post. Pls don't mind. Leak found E/Database( 4549): java.lang.IllegalStateException: mPrograms size 1 E/Database( 4549): at android.database.sqlite.SQLiteDatabase.finalize(SQLiteDatabase.java:1668) E/Database( 4549): at dalvik.system.NativeStart.run(Native Method) E/Database( 4549): Caused by: java.lang.IllegalSt...