In later versions of MySQL you can use the information_schema database to tell you when another table was updated: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = ‘dbname’ AND TABLE_NAME = ‘tabname’ This does of course mean opening a connection to the database. An alternative option would be to “touch” a particular file whenever the MySQL … Read more
If you’re using SQL Server 2005, you could use the FOR XML PATH command. SELECT [VehicleID] , [Name] , (STUFF((SELECT CAST(‘, ‘ + [City] AS VARCHAR(MAX)) FROM [Location] WHERE (VehicleID = Vehicle.VehicleID) FOR XML PATH (”)), 1, 2, ”)) AS Locations FROM [Vehicle] It’s a lot easier than using a cursor, and seems to work … Read more
SELECT train, dest, time FROM ( SELECT train, dest, time, RANK() OVER (PARTITION BY train ORDER BY time DESC) dest_rank FROM traintable ) where dest_rank = 1
In PostgreSQL 9.1 there is an easier way http://www.postgresql.org/message-id/162867790801110710g3c686010qcdd852e721e7a559@mail.gmail.com CREATE TABLE foog(a varchar(10)); ALTER TABLE foog ALTER COLUMN a TYPE varchar(30); postgres=# \d foog Table “public.foog” Column | Type | Modifiers ——–+———————–+———– a | character varying(30) |
You can query the USER_TAB_COLUMNS table for table column metadata. SELECT table_name, column_name, data_type, data_length FROM USER_TAB_COLUMNS WHERE table_name=”MYTABLE”
I needed to know this for sure, so I benchmarked both methods. I consistenly found IN to be much faster than using OR. Do not believe people who give their “opinion”, science is all about testing and evidence. I ran a loop of 1000x the equivalent queries (for consistency, I used sql_no_cache): IN: 2.34969592094s OR: … Read more
SELECT TIMEDIFF(‘2007-12-31 10:02:00′,’2007-12-30 12:01:01′); — result: 22:00:59, the difference in HH:MM:SS format SELECT TIMESTAMPDIFF(SECOND,’2007-12-30 12:01:01′,’2007-12-31 10:02:00’); — result: 79259 the difference in seconds So, you can use TIMESTAMPDIFF for your purpose.
If I were you, I would write a full description of what the query needs to do, including all the tables, fieldnames etc., and post that here. Someone here is bound to be able to write a new version of the query that is not copyright your developer, and you can edit it each year … Read more
Once you have removed your duplicate(s): ALTER TABLE dbo.yourtablename ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2); or CREATE UNIQUE INDEX uq_yourtablename ON dbo.yourtablename(column1, column2); Of course, it can often be better to check for this violation first, before just letting SQL Server try to insert the row and returning an exception (exceptions are expensive). Performance impact of … Read more
You can use GROUP BY SalesOrderID. The difference is, with GROUP BY you can only have the aggregated values for the columns that are not included in GROUP BY. In contrast, using windowed aggregate functions instead of GROUP BY, you can retrieve both aggregated and non-aggregated values. That is, although you are not doing that … Read more