I have the following tables in a relational database: Basically, ReadingSwitch and ReadingValue are subtypes of Reading and SwitchSensor and AnalogSensor are subtypes of Sensor. A reading can either be a SwitchReading or ValueReading value – it cannot be both, and a Sensor can either be an AnalogSensor …
Tag: database
How to group latitude/longitude points that are ‘close’ to each other?
I have a database of user submitted latitude/longitude points and am trying to group ‘close’ points together. ‘Close’ is relative, but for now it seems to ~500 feet. At first it seemed I could just group by rows that have the same latitude/longitude for the first 3 decimal places (roug…
Need to list all triggers in SQL Server database with table name and table’s schema
I need to list all triggers in SQL Server database with table name and table’s schema. I’m almost there with this: I just need to get the table’s schema also. Answer Here’s one way: EDIT: Commented out join to sysusers for query to work on AdventureWorks2008. EDIT 2: For SQL 2000
SQL WHERE clause matching values with trailing spaces
In SQL Server 2008 I have a table called Zone with a column ZoneReference varchar(50) not null as the primary key. If I run the following query: select ‘”‘ + ZoneReference + ‘”‘ as …
Name database design notation you prefer and why?
Which notation, methodology and tools for database designing, modeling, diagraming you prefer and why? Which notation, standards, methodology are the most broadly used and covered by different vendors? Which are standard and which are not? i.e. which are to stick with and which to avoid? And why do you prefer…
Ideal database for geo (map) data
I’m looking for suggestions for an ideal database or data structure for storing a map. Essentially, the map consists of “ways” which are like roads, paths, etc. Ways contain nodes (which have a latitude and longitude coordinate, and sometimes an altitude.) Any such database or structure: sho…
Use email address as primary key?
Is email address a bad candidate for primary when compared to auto incrementing numbers? Our web application needs the email address to be unique in the system. So, I thought of using email address …
Mysql Complex Where Clause
I really surprised when I tried below code in MySQL: It doesn’t work. I need to get product that is both free_shipping is ‘yes’ AND price equal ‘5’ at the same time. How can I create this query properly? Table Structure: contents: (TABLE 1) content_fields: (TABLE 2) Example (Get …
How to search for a substring in SQLite?
Whats the most efficient way to search for a sub string in SQLite? I’m looking at the LIKE operator. Do I have the right idea? Has this worked well for you? http://www.sqlite.org/lang_expr.html …
Query to group by maximum depth of PostgreSQL Ltree?
I want to query for all products with the name “Shania Twain”, but I want group them only by the tree with the deepest nlevel. Assuming I have a table like the following with the name categories So, for example, The problem lies with the HAVING clause requiring a boolean expression. The clause MAX…