To simplify, if I had a table called 'employee':
id INT NOT NULL PRIMARY KEY,
salary FLOAT,
department VARCHAR(255)
I want to perform and query where I retrieve the minimum salary in each department.
So my query is something like this:
SELECT employee.ID, MIN(employee.salary), employee.department
FROM employee
GROUP BY employee.depar...
Say, I have two tables like these:
Table group Table user
+----+-----------+ +----+----------+------+----------+
| id | groupname | | id | username | rank | group_id |
+----+-----------+ +----+----------+------+----------+
| 1 | Friends | | 1 | Frank | 1 | 1 |
| 2 | Family | | 2 | Mike | 3 | ...
This is the query I'm performing (without some Joins that are not relevant):
SELECT a.*, c.id
FROM a
LEFT OUTER JOIN b ON a.id = b.id_anunciante
LEFT OUTER JOIN c ON c.id = b.id_rubro
GROUP BY a.id
Each row of "a" is linked with 1 to 5 rows in "b".
The problem is that GROUP BY has performance issues (it takes 10x or more using GROUP ...
Here is a simplified version of my table
tbl_records
-title
-created
-views
I am wondering how I can make a query where they are grouped by title, but the record that is returned for each group is the most recently created. I then will order it by views.
One way I guess is to do a sub query and order it by created and then group it b...
SQLite behaves differently when dealing with aggregation than many other RDBMS's. Consider the following table and values:
create table foo (a int, b int);
insert into foo (a, b) values (1, 10);
insert into foo (a, b) values (2, 11);
insert into foo (a, b) values (3, 12);
If I query it thus:
select a, group_concat(b) from foo;
Norm...
Newbie SQL question here -->
I've got an Occurrences table that contains a row for each time a user did something. A user can do the thing multiple times per day. It looks like this:
Date Username
------ --------
1/1/9 User1
1/1/9 User1
1/1/9 User2
1/2/9 User1
1/2/9 User3
1/3/9 User1
1/3/9 User1
1/...
Hello All,
I'm currently working with a paradox database that was implemented before I started working at my current job at an insurance firm.
Long story short is that when I am trying to to compile a query of the debit/credit balances of all the active clients, it gives me a different balance per client if I do a query for each indivi...
I have a query which uses the GROUP_CONCAT of mysql on an integer field.
I am using PHPMYADMIN to develop this query. My problem that instead of showing 1,2 which is the result of the concatenated field, I get [BLOB - 3B].
Query is
SELECT rec_id,GROUP_CONCAT(user_id)
FROM t1
GROUP BY rec_id
(both fields are unsigned int, both are n...
Hey DBAs and overall smart dudes. I have a question for you.
We use MySQL VIEWs to format our data as JSON when it's returned (as a BLOB), which is convenient (though not particularly nice on performance, but we already know this).
But, I can't seem to get a particular query working right now (each row contains NULL when it should con...
what is the linq equivalent of the following:
SELECT Mytable.A, MyTable.B, SUM(MyTable.C)
FROM MyTable
GROUP BY Mytable.A, MyTable.B
ORDER BY Mytable.A, MyTable.B
This is trivial in SQL, but seems to be very difficult in LINQ. What am I missing?
...
I have the following XML code:
<root>
<options>
<companies>
<company url="http://www.brown.com">Brown LLC</company>
<company url="http://www.yellow.com">Yellow LLC</company>
<company url="http://www.black.com">Black LLC</company>
<company url="http://www.bourdeaux.com"...
I have a set of Data with columns such as below
OffName,RO1,RO2,RO3
To explain further i use sample data as below:
OffName RO1 RO2 RO3
A John Jack Rob
B Earl John Carl
C Rob Chris Kenny
D Rodney Carl Jacob
RO stands for Reporting Officer. Each Officer reports to upto 3 RO's.i need to make...
Let's say I have a table in SQL Server which contains the results of a query with an inner join.
The following XQuery:
select @code.query
(
'for $s in /root/row
return
<Foo Language="{data($s/@lang)}" Method="{data($s/@method)}" Returns="{data($s/@returnType)}">
<Bar ReferencedBy="{data($s/@callers)}" Static="{data($s/@static)}" />
</...
Hi,
This might be a bit difficult to explain but I have two columns in my SQL server database which I have simplified...
Items
ID
itemName
voteCount
score
Votes
itemID
score
So, basically I am storing every vote that is placed in one table, but also a count of the number of votes for each item in the item table along with it's averag...
I have 2 tables, campaigns and campaign_codes:
campaigns: id, partner_id, status
campaign_codes: id, code, status
I want to get a count of all campaign_codes for all campaigns WHERE campaign_codes.status equals 0 OR where there are no campaign_codes records for a campaign.
I have the following SQL, but of course the WHERE statement e...
I am new to mysql and I have been pulling my hair out about this problem for days. I need to improve/optimize this query so that it runs faster - right now its taking over 5 seconds.
Here is the query:
SELECT SQL_NO_CACHE COUNT(*) as multiple, a.*,b.*
FROM announcements as a
INNER JOIN stores as s
ON a.username=s.username
...
In order to remove the bias introduced by the differences in the number of days in the months and years (in case of leap years), from monthly total comparisons of arbitrary quantities and assuming, for example, a table named My_Table with a datetime column named order_date, and an integer one named revenue, I use the following query to g...
I'm (thoroughly) learning SQL at the moment and came across the GROUP BY clause.
GROUP BY aggregates or groups the resultset according to the argument(s) you give it. If you use this clause in a query you can then perform aggregate functions on the resultset to find statistical information on the resultset like finding averages (AVG()) ...
Hello,
I'm developing an auctions application in which bids are held in a table with the following format:
id | user_id | auction_id | placed_at
I'd like to group the bids by user id and select their counts, but only if the user ids appear one after another. The bids are ordered by placed_at descending. Here's what I mean:
1 | 1 | 1...
Hello,
I have the following three tables in a MySQL database in order to give ratings to comments of users:
Users:
id name
-----------
1 Smith
2 Brown
Comments:
id user_id post_id comment
-----------------------------------
1 2 1 Test 1
2 1 1 Test 2
3 1 1 ...