sql

Problem with MySQL query for high scores leaderboard

Hello, I have a MySQL high scores table for a game that shows the daily high score for each of the past days of the year. Right now I am doing a PHP for-loop and making a separate query for each day, but the table is becoming too large to do that so I would like to condense it into one simple MySQL statement. Here is my new query right...

How to import a mysql dump while renaming some tables/columns and not importing others at all?

I'm importing a legacy db to a new version of our program, and I'm wondering if there's a way to not import some columns/tables from the dump, and rename other tables/columns as i import them? I'm aware I could edit the dump file in theory, but that seems like a hack, and so far none of my editors can handle opening the 1.3 gb file (Yes...

Select records for MySQL only once based on a column value

Hi all, I have a table that stores transaction information. Each transaction is has a unique (auto incremented) id column, a column with the customer's id number, a column called bill_paid which indicates if the transaction has been paid for by the customer with a yes or no, and a few other columns which hold other information not relev...

Incorrect syntax error when accessing remote stored procedure

Hello again, StackOverflow - I'm still trying to deploy this site, but with every problem I solve, another arises. Anyways - I've set up the database at my hosting to allow remote connections, and it is running Sql Server 2005. On my development machine, I am working with Sql Server 2008. I've installed the asp.net schema on my hosted ...

Datamapper "first" method is querying the whole relation

I'm using Datamapper 1.0 with sinatra and sqlite3 as backend. I have some devices, which have multiple locations, and I usually only want the latest location of a device. The problem is that the generated SQL for the above line will query all locations for the device not just the latest one (and a device can have 10-20k locations). Thi...

what do I use to perform SQL-like lookups on a java table?

I have a 2D array public static class Status{ public static String[][] Data= { { "FriendlyName","Value","Units","Serial","Min","Max","Mode","TestID","notes" }, { "PIDs supported [01 – 20]:",null,"Binary","0",null,null,"1","0",null }, { "Online Monitors since DTCs cleared:",null,"Binary","1",null,null,"1","1",null }, { "Freeze DTC:",nul...

When I read other posts about this, I still can't see the difference in SQL between GROUP BY and ORDER BY. What is it?

I've started my own thread on this question so as to have less overhead from posting it on someone else's thread. I have trouble understanding in SQL what the difference is between GROUP BY and ORDER BY. I know there have been threads made about this, but they don't provide me with a useful answer. Here is something along the general ...

ASP.NET: connecting to a sql server database

(very newbie question, please help if you can) how do i connect visual web developer to sql server express 2008? in the Database Explorer, i right click on Data Connections, click Add Connection..., and in the Data source box i choose Microsoft SQL Server (sqlClient), which i'm guessing doesn't connect me to a database file, but to sql ...

Sql naming best practice

I'm not entirely sure if there's a standard in the industry or otherwise, so I'm asking here. I'm naming a Users table, and I'm not entirely sure about how to name the members. user_id is an obvious one, but I wonder if I should prefix all other fields with "user_" or not. user_name user_age or just name and age, etc... ...

What's the difference between table-based relations and column-based relations in RDBMS?

Hi, I have used column-based relations a lot in my projects like: CREATE TABLE `user` ( id INT AUTO_INCREMENT PRIMARY KEY, usergroup INT ); CREATE TABLE `usergroup` ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); however, at work it seems some people do it using table-based relations like this: CREATE TABLE `user` ( id INT...

Which is better: to sort results at the database level or at the application level

Hi all suppose we have a page in a site that displays some records from a database. we need to display the records sorted by some column. which approach gives better performance: to retreive the data sorted from the database or to apply sorting on the grid ? thanks ...

How can I stop System.OutOfMemoryException on bulk insert.

Hi I am bringing about 100000 records into memory cleaning the data and inserting into a new table. After inserting around 2000 records I get the following exception. A first chance exception of type 'System.OutOfMemoryException' occurred in Iesi.Collections.DLL A first chance exception of type 'FluentNHibernate.Cfg.FluentCon...

textbox string to long format error

hi, i am doing a project with SQL server 2005 and VS 2008, I have parsed a textbox text to long variable(phone number), because the backend stores the phone number in bigint. long phone = long.Parse(TextBox4.Text); This works fine when i insert my phone number, but if a person doesn't know or doesn't want to insert it, i have the...

Escape Character in SQL

Hi, Please forgive me as I am a bit of an sql noob. I am trying to do an insert using the following but am having a problem with the apostrophes. I have a lots of records to insert but many have the same problem. Is there a way of escaping them? INSERT INTO [dbo].[tb_Chefs] ([ChefHotelID], [HotelID], [ChefID], [Position], [Gro...

How to get Date diffrence in hh:mm format In Select Query Sql 2005

Hi I am trying to get the the result of in time and out time from dates but it returns only hours using following select Query as follows SELECT DATEDIFF(Hh,InTime,OutTime) as Diff_time from EmpLogTable and i need result in HH:MM Suppose my in time is 11 am and out is 5.49pm so o/p would be 6.49 but using above select query i ...

Transact Sql LEFT function weird output

select replace(stuff('123456',2,2,'ABCD'),'1',' ') select LEFT('ABCD456',4) select left(replace(stuff('123456',2,2,'ABCD'),'1',' '),4) Ok now the first select outputs 'ABCD456', the series of functions evaluates to that exactly the first parameter to the left function in the second select second select returns 'ABCD' as expected thir...

Access count on another table doesn't work

I have a Posts table and PostComments table of a blog system. I want to count and sort the posts by comment count but my query won't work.: SELECT Posts.PostID, Posts.DateCreated, Posts.Title, Posts.Description, Posts.Hits, (SELECT Count(CommentID) FROM PostComments WHERE PostComments.PostID=Posts.PostID AND PostComments.IsApproved=Tru...

Uploading ASP.NET 3.5 website

I have a website writen in ASP.NET and C#. The site works when run locally, however I don't have any experience with uploading. Recently I got a free hosting package from somee.com and am currently trying to upload it. Basically, the pages that don't require database connection work, the ones that do don't. So, I've created a database ...

DataReader, DataColumn - Can These Show Me Columns In Schema Such as SomeSchema.SomeColumn?

I have a database table where columns are in multiple schemas. Example: SomeTable SomeSchema.Column1 SomeSchema.Column2 AnotherSchema.ColumnA AnotherSchema.ColumnB I have code using a DataTable.Rows[r][c].ToString() syntax and it looks like only the Column1 or ColumnA part is being returned. I need to get the full name SomeSchema.Co...

Query with multi tables

I have four tables: characters arena_team arena_table_member arena_team_stats. characters table has guid, name arena_team table has arenateamid, name, type arena_table_member table has guid(this is the same as in characters table), arenateamid arena_team_stats table has arenateamid, rating, wins, wins2, played How do I get the li...