I have a MySQL database that looks like this: users ( id , name ) groups ( id , name ) group_users ( id , group_id , user_id ) Now to look up all the groups that a user belongs to, I would do something like this: select * from 'group_users' where 'user_id' = 47; This will probably return something like: ( 1 , 3 , 47 ),...
Hi everyone, I want to select all distinct order_ids from my table, and order that list by the date column. Using DISTINCT is of course a query-wide parameter, so trying something like this doesn't work: SELECT DISTINCT(orderId, datetime) FROM table ORDER BY datetime DESC This returns all DISTINCT combinations of the orderId and da...
I'm writing a function in SQL that we can use to validate First/Middle/Last names we have in a given table. At the moment I'm working on a list of ascii codes for characters that I'm going to consider invalid and strip out of the input. My plan is to create a table which contains the character codes of those characters I consider to be...
I've got a table that looks like this: Code Mark Date Notional Beta 5/1/2008 $1,102,451.00 Beta 5/2/2008 $1,102,451.00 Beta 5/5/2008 $1,102,451.00 Beta 5/6/2008 $1,102,451.00 I need to create a table that has all of the Mark Dates in one column and the difference between each adjacent Mark Date when sorted in another co...
LS, Since the Microsoft ODBC Excel Driver doesn't support ALTER TABLE/DELETE FROM(/UPDATE?) statements, I'm using alternative ways of getting the right data in the right place. I still have one problem with getting 2 columns added to an excel sheet. Someone told me to use: SELECT *, newcol1, newcol1 INTO newExternalSheet FROM thisShe...
I'm trying to write a query that returns the most recent GPS positions from a GPSReport table for each unique device. There are 50 devices in the table, so I only want 50 rows returned. Here is what I have so far (not working) SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1 RIGHT JOIN (SELECT DIST...
ALTER TABLE Physician Modify (RefLastName nvarchar(500), RefFirstName nvarchar(500)); Getting Incorrect syntax error... ...
i want to check for a particular set of values. eg check columnname should be between 1 to 5 check columnname should be either 1 or 2 or 4 ...
I am creating a table to summarize data that is gathered from about 8 or so queries that have very light logic/WHERE clauses and all select against different tables. I was wondering what the best option would be to fetch the summarized data: One query with multiple JOINS to gather all relevant information A stored proc that encapsulat...
I built a dialog so that when the user decides to print the DataGridView, he can select the date range for which to print. This works fine; however, either I am missing something or the filtering is not working properly. In order to filter by a range and for it to include the correct days, you have to select one day prior to the actual d...
Can anyone point out some good LINQ training resources. Mostly interested in getting some developers that are very skilled with SQL up to speed using Lambda and LINQ queries. They are struggling with some of their more advanced queries and fall back on ExecuteQuery() to kind of do the LINQ thing. Queries that used to be easy in TSQL but ...
Hello, Lets say I have this table -------------------------------------------------------------- | ID | DATE | GROUPNAME | RESULT | INFO1 | INFO2 | -------------------------------------------------------------- | 1 | 01/06 | Group1 | 12345 | Abc | xxxx | | 2 | 01/04 | Group2 | 54321 | AAA ...
I have a table named activity with 2 columns: when as datetime // last time i saw a user guid as varchar // a unique identifier for each user This tables is used to track usage in a certain resource. Guid are created by the clients. I want to be able to query how many new users I have in the last day/week/month. A new user is identi...
In a [member] table, some rows have the same value for the email columns. login_id | email john [email protected] peter [email protected] johnny [email protected] ... Some people used different login_id but the same email address, no unique constraint was set on this column. Now I need to find these rows and see if they should be ...
I have a table with INT id and DATETIME date, amongst other fields. Rows are inserted into this table each weekday (not guaranteed), and several other tables use this id as a foreign key. My question is, how can I get the id for the max date of each month, which I can then use to join to other data tables? For example, if the process ...
I have been struggling with a problem that only happens when the database has been idle for a period of time for the data queried. The first query will be extremely slow, on the order of 30 seconds and then related queries will be fast like 0.1 seconds. I am assuming this is related to caching, but I have been unable to find the cause ...
Given a SQL table Transactions ID INT COMPANY_ID INT STATUS INT where STATUS IN (0,1) indicates a free transaction and STATUS IN (2,3) indicates a billable transaction, what simple (I hope) ANSI SQL statement will show me, per COMPANY_ID, the number of billable transactions, non-billable transactions, and th...
I am learning SQL for a personal projects and seems that I don't quite get the COUNT function. I have a "sample" table with this sample data: NAME COLOR Tom red Tom blue Jerry yellow Keri yellow Paul red Bob yellow Bob red Mary green What I am attempted to do is print out only those NAME values that have only one COLOR va...
SQL Server has a way to let you "export" an existing table's schema, in the form of its necessary SQL "Create Table" commands. How do you do this in Oracle? Is there a nice automated way of doing it? ...
Data: EmpNumber, TimeStamp, AreaName 10632, 2009-11-23 16:40:33.000, OUT_1 10632, 2009-11-23 16:39:03.000, IN_1 10632, 2009-11-23 16:38:56.000, IN_1 10632, 2009-11-23 15:31:51.000, OUT_1 10632, 2009-11-23 15:31:48.000, IN_1 10632, 2009-11-23 15:31:43.000, IN_1 10632, 2009-11-23 15:31:14.000, OUT_1 1063...