Formatting Numbers in SQL Server
How can I format the number 123.45678 to 123.45 in SQL Server 2008? ...
sql
How do I JOIN aggregations results from several SQL SELECTS?
I have a MEMBERS table with the following relevant columns: Name JoinDate Level --1=Gold,2=Silver,3=Bronze** I want to create a single query to return a membership summary that lists the total number who joined by year and by membership level. Basically, the columns in my resultset would be something like this: | YEAR | ...
Update function in TSQL trigger
I have a question about TSQL function Update. For example, I have a table with a field Name. If I check if the field Name is changed or not in a After Update trigger likes this: if Update(Name) Begin -- process End Will the Update still return TRUE even if Name is not changed? The following update statement will update it wi...
Aggregate function and other columns
Hello it is possible for a SQL query to return some normal columns and some aggregate ones? like : Col_A | Col_B | SUM 5 6 7 ...
SQL adding composite primary key through alter table option
I have already created a table to the database. Table is something like following (I am using sqlite on mac for iPhone dev) create table ( attendanceDate varchar, studentNo integer, absentDetail varchar ); Now I have already committed this statements. What I need is the following: Add a composite primary key (attandanc...
Database structure for items with varying attributes
Hello there. I am developing a clothes web application and would appreciate advice on how to structure the data in my mysql database. Every product (item of clothing) will be photograped in a number of ways, let's call them 'modes'. For example a shirt would be photographed buttoned or unbuttoned, and/or tucked in/not tucked in. A pa...
delete selected rows in table ( How do i increase the speed )
Back story i have a table that stores cached times, and currently has about 1m rows. And when i update the table with new versions of the cached items i need to delete the old cached items ( About 3k items ). Its not critical that these items are deteted right away but i would prefere it, as when clients get out cached items i would like...
DDL for SQL Membership/Roles Provider
Where can I get the DDL to add SQL Roles and Membership to an existing SQL Server database? I want to use the providers but I'd like to manually run the scripts. ...
SQL (for mysql) getting good results, but need to exclude some columns
I'm trying to write a custom search that will search "all categories" and individual categories (via a form/select dropdown). I'm having problems with my individual category search. When I test my sql, it returns good results, but I'm getting extra/unwanted categories in my results. I'm getting those extra categories in my results due...
complex sql order by
id | message | reply id | date 1 | my new app.. | 0 | 10 / 10 / 2009 (latest message on top, follow by replies) 5 | love ur app.. | 1 | 11 / 10 / 2009 (this should show under the main message) 6 | another comm | 1 | 12 / 10 / 2009 2 | application 2 | 0 ...
How do I return rows with a specific value first?
I want my query to return the rows of the table where a column contains a specific value first, and then return the rest of the rows alphabetized. If I have a table something like this example: - Table: Users - id - name - city - 1 George Seattle - 2 Sam Miami - 3 John New York - 4 Amy New York - 5 Er...
How do you concat multiple rows into one column in SQL Server?
I've searched high and low for the answer to this, but I can't figure it out. I'm relatively new to SQL Server and don't quite have the syntax down yet. I have this datastructure (simplified): Table "Users" | Table "Tags": UserID UserName | TagID UserID PhotoID 1 Bob | 1 1 1 2 ...
How can I calculate the top % daily price changes using MySQL?
I have a table called prices which includes the closing price of stocks that I am tracking daily. Here is the schema: CREATE TABLE `prices` ( `id` int(21) NOT NULL auto_increment, `ticker` varchar(21) NOT NULL, `price` decimal(7,2) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `tick...
Why not always use GUIDs instead of Integer IDs?
What are the disadvantages of using GUIDs? Why not always use them by default? ...
Inserting Missing Rows
I've got an interesting SQL puzzle I haven't been able to solve, hopefully one of you have more SQL-Fu than I do. I have two tables set up something like this: tblFormElement FrmElementID Label tblFormSubmissionElement FrmID_fk FrmElementID_fk SubmitID_fk SubmissionValue And if I run the following SQL: SELECT...
Querying Active Directory from Sql Server 2005
How I can query Active Directory from Sql Server 2005? ...
INSERT INTO..SELECT..ON DUPLICATE KEYS ambiguous ids
Hey there, I have the following table: mysql> SELECT * FROM `bright_promotion_earnings`; +----+----------+------------+----------+-------+ | id | promoter | generation | turnover | payed | +----+----------+------------+----------+-------+ | 1 | 4 | 1 | 10 | 0 | | 3 | 4 | 5 | 100 | ...
LINQ-to-Entities or Entity SQL query
I need a LINQ-to-Entities or Entity SQL based query to handle the following scenario: Users belong to one or more Locations. Shipments belong to exactly one Location. Batches contain Shipments. Users should only see Batches where ALL of the Shipments in the Batch are from Locations they have access to. Example Batch: Shipment_1.Lo...
Sorting records in a 1:n (one-to-many) relationship
I've got 2 tables: +-----------+ +------------------------------------+--------------+ + persons | | photos | +-----------| +---------------------------------------------------+ + id | name + | id | person_id | path | title | +-----------+ +---------------------------...
SQL Server triggers "For Each Row" equivalent
I need to update multiple rows in a Parts table when a field in another table changes and I want to use a trigger. The reason for the trigger is many existing application use and modify the data and I don't have access to all of them. I know some databases support a For Each Row in the trigger statement but I don't think Microsoft does. ...