LIMIT and UNION ALL not returning the requested number of records

I have a table with multiple type values and I'm wanting to get a sample records from some of them.

My current query is as follows:

-- Pulling three sample records from each "type"
SELECT * FROM example WHERE type = "A" LIMIT 3
UNION ALL
SELECT * FROM example WHERE type = "B" LIMIT 3
UNION ALL
SELECT * FROM example WHERE type = "C" LIMIT 3
;

I expect this to return a total of 9 records; 3 from type = "A", 3 from type = "B", and 3 from type = "C".

However, the result that I actually receive is 3 records from type = "A" and nothing else.
I know for a fact that the other type values exist because I can run the individual SELECT statements and they return results.

Why is MySQL only returning 3 records and how can I have it return the full 9 records that I want?

I've created a SQL Fiddle to illustrate the issue: http://ift.tt/1SIJUZB

via Chebli Mohamed

Create Table with Parameters

I would like to create an Excel table, using OleDb:

OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.Connection = oleDbConnection;

String commandText = "CREATE TABLE" + " [" + sheetModel.Sheet.Name + "] ";
commandText += "(";

for (int index = 0; index < spalten; index++)
{
    String _header = sheetModel.DT1.Rows[heaader].ItemArray[index].ToString();

    oleDbCommand.Parameters.Add(new OleDbParameter("@var" + (index + 1).ToString(), _header));

    if (index > 0)
    {
        commandText += ", ";
    }

    commandText += "@var" + index.ToString() + " VARCHAR";
}
commandText += ");";


try
{
    oleDbCommand.CommandText = commandText;
    oleDbCommand.ExecuteNonQuery();
    oleDbCommand.Parameters.Clear();
}
catch (Exception exception)
{
    MessageBox.Show(exception.Message);
    return;
}  

The result for the Excel table is,

@var0 @var1 @var2 @var3 @var4 @var5

but I should look like this:

"PREIS/Stk. EURO" "Stk." "" "Produkt" "Artikelmerkmale" "Sonstige"

Where is my mistake?
The code is working fine for inserting values.

via Chebli Mohamed

Need to fetch a row with minimum value in postgreSQL

I need a row with a minimum price value in which product_id will be unique. Look at the below structure which is used so far.

Consider stock of the all row are "1";

Price Table:

product_id  | place_id | price    | special_price | special_date_from   | special_date_to
--------------------------------------------------------------------------------------------
27          |27        |1000.0000 |0.0000         |                     |
26          |27        |500.0000  |129.0000       |2015-05-15 00:00:01  |2015-08-30 23:59:59
26          |24        |1500.0000 |0              |                     |
27          |5         |56224.0000|0              |                     |
27          |128       |1000.0000 |100.0000       |2015-07-31 00:00:01  |2015-08-12 23:59:59
27          |121       |100.0000  |0              |                     |
26          |121       |500.0000  |0              |                     |

My Query IS:

select * 
  from ( 
        (select min(price) price, 
                myt.product_id 
           from ( select (case when 
                              (cpp.special_price_fromdate <= '2015-08-04 19:18:49' 
                               and cpp.special_price_todate >= '2015-08-04 19:18:49' 
                               and cpp.special_price > 0) 
                               then cpp.special_price else cpp.price end
                          ) as price,  
                          cpp.product_id, 
                          cpp.place_id
                   from product_price as cpp 
                  where cpp.in_stock > 0   
                    and cpp.place_id IN (130,27,128,129,126,121,54)
                ) as myt group by product_id
        ) t1 
inner join
    (select DISTINCT on(pps.product_id) 
            (case when (pps.special_price_fromdate <= '2015-08-04 19:18:49' 
                        and pps.special_price_todate >= '2015-08-04 19:18:49' 
                        and pps.special_price > 0) 
                  then pps.special_price 
                  else pps.price end) as price, 
            pps.product_id,
            pps.price as old_price, 
            pps.place_id 
       from product_price pps 
      where pps.in_stock > 0
    ) t2 on t1.price = t2.price 
            and t1.product_id = t2.product_id 
            and t1.product_id in ('26','27')
) AS "pp";

I want the results to be:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000      
27          | 121      | 100.0000 | 100.0000

But i get the results based on the above query:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000

27 product_id has been skipped because of equal price which i have checked in "On Condition". I dont know why :(

via Chebli Mohamed

Selecting Multiple ID's in one Select

I have a Database with entries that have to be grouped togethe

id | Name    | Surname | Time
1  | Michael | Kane    | 3
2  | Torben  | Dane    | 4
3  | Dinge   | Chain   | 5
4  | Django  | Fain    | 5
5  | Juliett | Bravo   | 6
6  | Django  | Fain    | 7
7  | Django  | Fain    | 3
8  | Django  | Fain    | 4
9  | Dinge   | Chain   | 4
10 | Torben  | Dane    | 4

Now I want to group the items while maintaing all Id's. I'm comming close with the following query but I am lossing my ids

SELECT id, Name, Surname, sum(Time) from Names group by(Name)

The Result of the Query is

 id | Name    | Surname | Time 
 9  | Dinge   | Chain   | 9
 8  | Django  | Fain    | 19
 5  | Juliett | Bravo   | 6
 1  | Michael | Kane    | 3
 10 | Torben  | Dane    | 8

while I would need all ids like this

 ids    | Name    | Surname | Time 
 3,9    | Dinge   | Chain   | 9
 4,6,78 | Django  | Fain    | 19
 5      | Juliett | Bravo   | 6
 1      | Michael | Kane    | 3
 2,10   | Torben  | Dane    | 8

How can i accomplish this?

via Chebli Mohamed

Incorrect syntax near 'Name'

I getting errors:

Incorrect syntax near 'nvarchar'.
Incorrect syntax near 'Name'.

Please help to get from this.

I also added scalar to the names (@) but I am not getting anything.

public partial class Form1 : Form
{
    SqlCommand cmd;
    SqlConnection con;

    private void button1_Click(object sender, EventArgs e)
    {
        con = new SqlConnection(@"Data Source=DELL_LAPTOP\sqlexpress;Integrated Security=True");
        con.Open();

        cmd = new SqlCommand("Insert Into newproj (Name,Designation,Gender,Age,Address,Date,Staff Name,Shift,ST,ET,Hours) Values (@Name,@Designation,@Gender,@Age,@Address,@Date,@Staff Name,@Shift,@ST,@ET,@Hours)", con);
        cmd.Parameters.Add("@Name", textBox4.Text);
        cmd.Parameters.Add("@Designation", textBox2.Text);
        cmd.Parameters.Add("@Gender", comboBox1.SelectedItem.ToString ());
        cmd.Parameters.Add("@Age", textBox3.Text);
        cmd.Parameters.Add("@Address", textBox5.Text);
        cmd.Parameters.Add("@Date", dateTimePicker1.Text);
        cmd.Parameters.Add ("@Staff Name", textBox1.Text);
        cmd.Parameters.Add ("@Shift", comboBox2.SelectedItem.ToString());
        cmd.Parameters.Add("@ST", textBox7.Text);
        cmd.Parameters.Add("@ET", textBox8.Text);
        cmd.Parameters.Add("@Hours", textBox6.Text);

        cmd.ExecuteNonQuery();       
    }
}

via Chebli Mohamed

Sum result of SELECT...WHERE in SQL SERVER

Can someone tell me what I'm doing wrong, and if I can get the expect result... (Keep in mind this is a VIEW)

    SELECT
      [Id]
    , [Nome]
    , [Estado]
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc = 'A1.' OR EstProc = 'A2.') AND LI.LogImpFiles_Id = LIF.Id) AS ItemsProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE EstProc = 'A3.' AND LI.LogImpFiles_Id = LIF.Id) AS ItemsErroProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc= 'A4' OR EstProc= 'A5') AND LI.LogImpFiles_Id= LIF.Id) AS ItemSucessoProcessamento
    , SUM(ItemsErroProcessamento + ItemSucessoProcessamento) AS [ItemsProcessados]
    , [CreatedOn]
    , [CreatedBy]
FROM
    [dbo].[LogImpFiles] AS LIF
group by [Id], Nome, Estado, CreatedOn, CreatedBy

The result is this:

1   TesteImport1        6   2   3   0   2015-08-04 15:41:41.5130000 110032797

I was expecting something like this:

1   TesteImport1        6   2   3   **5**   2015-08-04 15:41:41.5130000 110032797

via Chebli Mohamed

using mysql query to fill the rows of an empty table

I need to analyze some data in mysql and I'm trying to create a new table based largely on an existing table. Thus far I've created the table with a few dozen or so necessary columns. They are not identical to the columns (I could easily change this) in the query, but every column in the new table is contained in the query. The columns in the query and new table are also not found in the exact same order, but I could also change this if need be. How should I go about filling my table with all the necessary data from the query--100,00ish rows? Here is the format of the query:

select t2.name as agent,t1.* from
(select s.year, s.wk, t.* from sales s 
join transaction t
on s.id = t.id) t1,

(select s.year, s.wk, t.* from sales s 
join transaction t
on s.id = t.id) t2 

where t1.id=t2.id
and 
t1.name<>t2.name

via Chebli Mohamed