Android: Get highest value in column
I have an URL pointing to content and I need to get highest value contained in one of the columns. Is there any aggregate function that will accomplish that or do I have to do this manually? ...
sql
problems with update logic for a mysql db
I have the following PHP form, which posts back to a mysql database. My problem is that the update query seems to work, but is always overwritten with "checked". What I want to do is check is get the current value from the database, and then if there is a value in post, get that instead. Now...why is this not working? Do I need to have a...
creating SQL command to return match or else everything else
hi, i have three checkboxs in my application. If the user ticks a combination of the boxes i want to return matches for the boxes ticked and in the case where a box is not checked i just want to return everything . Can i do this with single SQL command? ...
How can I sum the last 15 rows in my database table?
I have a Sales table, with the following columns: employeeID amount date Now I want to SUM up the last 15 rows, so I am currently doing: SELECT TOP 15 SUM(amount) FROM Sales ORDER BY [Date] DESC But I get 15 rows obviously, is there a way I can sum it up and not have to loop through and SUM it on the client side? ...
How do I get first unused ID in the table?
Dear all, I have to write a query wherein i need to allocate a ID (unique key) for a particular record which is not being used / is not being generated / does not exist in database. In short, I need to generate an id for a particular record and show it on print screen. E. g.: ID Name 1 abc 2 def 5 ghi So, the thing is that...
faster way to use sets in MySQL
I have a MySQL 5.1 InnoDB table (customers) with the following structure: int record_id (PRIMARY KEY) int user_id (ALLOW NULL) varchar[11] postcode (ALLOW NULL) varchar[30] region (ALLOW NULL) .. .. .. There are roughly 7 million rows in the table. Currently, the table is being queried like this: SELECT * FROM custom...
Self-join of a subquery
Hi all, I was wondering, is it possible to join the result of a query with itself? (I'm using PostgreSQL) ...
Can I get the position of a record in a SQL result table?
If I do something like SELECT * FROM mytable ORDER BY mycolumn ASC; I get a result table in a specific order. Is there a way in SQL to efficiently find out, given a PK, what position in that result table would contain the record with my PK? ...
Import non-english WIkipedia XML dump in MySQL?
Hi, I'm looking for a script that can convert the XML wikipedia DUMP in MySQL compliant SQL. I've found a ready SQL dump for the english version but no easy way to import the other languages. Thanks, Piero ...
Stored procedure parses correctly but will not execute. Invalid object name. Msg 208
I've scripted up a stored procedure as follows. It will parse without errors, but when I try to execute it, it will fail. The error message reads: Msg 208, Level 16, State 6, Procedure aspnet_updateUser, Line 23 Invalid object name 'dbo.aspnet_updateUser'. Here is the stored procedure. USE [PMRS2] GO /****** Object: StoredProced...
Postgres to SQL Server
I'm attempting to retool myself more towards .NET (coming from an opensource background) and am looking at learning Microsoft SQL Server to complement my skills in C#. How easy would it be to transfer my skills from Postgres to Microsoft SQL Server? Furthermore would a book on SQL Server 2005 (Database Essentials - Step by Step, Microsof...
selecting and displaying ranked items and a user's votes, a la reddit, digg, et al
when selecting ranked objects from a database (eg, articles users have voted on), what is the best way to show: the current page of items the user's rating, per item (if they've voted) rough schema: articles: id, title, content, ... user: id, username, ... votes: id, user_id, article_id, vote_value is it better/ideal to: select ...
Does making a column unique force an index to be created?
In SQL Server 2005+ (I use both), does adding the UNIQUE constraint to a column automatically create an index, or should I still CREATE INDEX? ...
How do I get prices of each invoices?
Hi, I am developing a back-end that process about 500 invoices a day. I have following tables in my database. invoices invoice_id int primary_key auto_increment user_id varchar(10) invoice_type enum { package, document } users user_id int primary_key auto_increment rate_package_id int rate_document_id int rates rate_id int rate_nam...
SQL sort with limit? (non trivial)
table { id: long name: string } 1235 Fred 1902 Trever 5123 George 6467 Derek 7868 Joe 8972 Bob 9272 Alf 9842 Hank I want to return 2 records prior to that of Joes, in ascending order. i.e The correct values should be: 5123 George 6467 Derek Any thoughts? FYI: Returns incorrect rows: select * from table with id<7868 order by...
SQL Server query grouped by max value in column
**Update: using the Rank() over partition syntax available in MS SQL Server 2005 does indeed point me in the right direction, it (or maybe I should write "I") is unable to give me the results I need without resorting to enumerating rows in code. For example, if we select TOP (1) of rank, I get only one value, ie., slot 1. If I use MA...
Quickly select all rows with "1 or more" matching rows in another table
I'd like to select all rows from one table which match "one or more" rows in another table, in the most efficient way. SELECT identity.id FROM identity INNER JOIN task ON task.identityid=identity.id AND task.groupid IN (78, 122, 345, 12, 234, 778, 233, 123, 33) Currently if there are multiple matching tasks this returns the same i...
In Memory OleDbConnection to Excel File
I would like to open an OleDbConnection to an Excel file that's in memory as a FileStream. Does anyone know if this is possible and how to accomplish it? Big picture I would like to pass the file bytes into a .NET CLR Stored Procedure and extract the data. Perhaps there's a better way to do this that I've not considered. Thanks in ad...
Rails combined ('AND') searches on associated join tables
I cant get rails to return combined ('AND') searches on associated join tables of an Object. E.g. I have Books that are in Categories. Lets say: Book 1: is in category 5 and 8 But I cant get 'AND' to filter results using the join table? E.g ::-> Class Books has_and_belongs_to_many :categories, :join_table => "book_categories" Book.fi...
select rows that do not have any foreign keys linked
i have 2 tables Group and People People has GroupId that is linked to Group.GroupId (primary key) how can I select groups that don't have any people? in t-sql and in linq thank you ...