sql

How to select first 10 elements from database using SQL?

There is a query like SELECT * FROM clients ORDER BY id. I want to select only first 10 elements. How can I do this? P.S. I'm using MySQL. ...

CakePHP Pagination with conditions on has_many

So I have a User table and a History table with User hasMany Histories, and I'm trying to implement pagination on the user table. My problem is that I have search, and some of the things one can search by are things in the History table. Is there a way to filter pagination results based on data in a table associated by hasMany? Containa...

Updating row of table Using data from multiple columns of another table.

I have following table which I want to update using another table, given below. I want to update Null values of above given table using following table on the basis of ProductId. The updated table should be like this. I have mentioned ProductId in these table just for example. I don't know exact ProductId. It could be any ProductId....

TSQL: Any benefits for explicitly specifying NVARCHAR in a string?

When you add pass a new job_type to sys.sp_cdc_add_job @job_type, (which is of type nvarchar(20)) You can pass the argument as either N'cleanup' cleanup Are there any reasons or benefits to use the former syntax using N' to pass the argument to stored procedures? ...

SQL return multiple rows from one record

This is the opposite of reducing repeating records. SQL query to create physical inventory checklists If widget-xyz has a qty of 1 item return 1 row, but if it has 5, return 5 rows etc. For all widgets in a particular warehouse. Previously this was handled with a macro working through a range in excel, checking the qty column. Is there ...

SQL Server 2008 localization of tables

I need to localize a SQL Server 2008 database. After investigating recommendations, I have found that it is best to have separate tables or each of the languages for the strings. That way different sorting settings can be set for each table. For example, a typical Product table has ProdID, Product Description, and Price fields. The r...

Select 2 COUNT()'s from MySQL query

I am working on a rankings page for a game and am looking to order the rankings first by wins, and then by losses (in case of people having the same number of wins). The following query works fine in order to make a list in order by wins, but I am not sure how to put losses into this query. SELECT username, COUNT(id) AS wins FROM tb...

Can calculated column be used in another calculated column?

SELECT ExchangeRatePrice = CASE pp.Price WHEN NULL THEN 0 ELSE (CASE WHEN c.CurrencyId = 1 THEN pp.Price ELSE CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate)) END) END , price as OriginalPriceInDB, 10 * Price as CalculatedPrice, c.currencyid as Currency FROM ProductPrice pp, currency c I w...

Axapta v3.0 with SQL 2005?

I managed to find an old Axapta version to practice some programming and learn Axapta a little. The problem is I can't get it started. I had installed SP1-2-3 and started it, it is supposed to create the database. Well it creates some tables then I get a syntax error from SQL Server. I am told even SP1 was enough for SQL 2005. The error ...

Converting String to bit before inserting into the database

Can anyone help in converting string value in C# to bit equivalent in Sql. I'm trying to bulkcopy the values of a datatable into Sql table. All the values I've in the datatable are in string format. When I try to bulkcopy to SQL table I'm getting an error for bit datatype columns.Can anyone please post the C# code to convert string to...

How can I optimize this SQL query to get rid of the filesort and temp table?

Here's the query: SELECT count(id) AS count FROM `numbers` GROUP BY MONTH(created_at), YEAR(created_at) ORDER BY YEAR(created_at), MONTH(created_at) That query throws a 'Using temporary' and 'Using filesort' when doing EXPLAIN. Ultimately what I'm doing is looking at a table of user-submitted tracking numbers and co...

Programmatically generate script for all objects in a database

For an automated setup build that generates the setup for an application which uses Microsoft SQL Server, I am currently evaluating whether the following is possible: I want to programmatically (CMD script or C# code) execute the function "Generate Scripts" on a database that is accessible from Microsoft SQL Server Management Studio 200...

Best practice: Searching table against day, month or year..

I've got a table with a "date" column, where a user input will be queried against (using stored procedure)..and results will be shown on a datagrid.. now a user can either enter a year, Year/month , Year/month/day.. (from drop down lists) i know there r many possible ways to handle the different queries.. however i am trying to figure ...

Asp.net web application with MySQL

I see a lot of asp.net projects coupled with mssql2005 and mssql2008. Most of them don’t use MySQL for some reason.This is even true when an ORM like nhibernate is being used. Is this a pattern only to me or are there quite a lot of asp.net projects with MySQL as the db? If this is true, what might be the reason for not choosing M...

Reportins services Timeout expired

Hi, I'm developing a Report Service and while excecuting the query getting : An error has occurred during report processing. Exception has been thrown by the target of an invocation. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I know that on the SQL server th...

Django/SQL: keeping track of who who read what in a forum

I'm working on a not-so-big project in django that will among other things incorporate a forum system. I have most of the system at a more or less functioning state, but I'm still missing a feature to mark unread threads for the users when there are new posts. The thing is I can't really think of a way to properly store that informatio...

COUNT(id) vs. COUNT(*) in MySQL

Is there a difference between the following queries, assuming there is a primary field 'id' in the table (as in speed, etc)? SELECT COUNT(id) FROM table vs. SELECT COUNT(*) FROM table ...

Retrieving image from sql database

Hi all, Previously i had problem with inserting image into sql database. Now i have solved this problem and able to insert image in sqldatabase. Now I am facing problem with retrieving the image from database table. Here is my retrieving code: showimage.ashx: <%@ WebHandler Language="C#" Class="ShowImage" %> using System; using System.W...

how to calculate sum of hours?

My tables are as follows workers: id integer ... days: id INTEGER event_date DATE worker_id INTEGER and hours: id INTEGER hr_start VARCHAR2(8) hr_stop VARCHAR2(8) day_id INTEGER I need a function which will calculate for every user in database, sum of hours in current month. ...

"Field 'COUNT(id)' has no dataset" TSQLQuery in Delphi

Hi! Sorry for my english, but i hope you'll understand me :P I'm trying to create new TSQLQuery component in code, without placing it on form. I wrote that code: var sql:tsqlquery; pole:TFMTBCDField; .... sql:=tsqlquery.Create(self); sql.SQLConnection:=ddm.konekszyn; sql.SQL.Text:='SELECT COUNT(idrap) FROM raporty WHERE idkie...