Tagged Questions

Structured Query Language is a language for interacting with relational databases. Read the tag wiki's guidelines for requesting SQL reviews: 1) Provide context, 2) Include the schema, 3) If asking about performance, include indexes and the output of EXPLAIN SELECT.

learn more… | top users | synonyms

0
votes
1answer
17 views

Django ListView with MySQL query for the queryset

(Note: Even if you don't know Django, it's probably the query that needs work anyway, so you probably don't need to know Django/Python) I have a ListView that ...
2
votes
3answers
257 views

For a query over multiple databases, is it faster to do GROUP BY first or UNION first?

Having several databases with the same table, I need to group the union of the records. The xTables are stored in separate databases named by the year (this is by ...
2
votes
1answer
34 views

MySQL case statement with two tables

How can I improve the following working SQL statement for better performance and consistency? ...
3
votes
1answer
55 views

Getting a value from a database table dynamically

I am trying to create a forum using php in a MVC architecture. This is my database setup: Table: forum_categories ...
2
votes
1answer
17 views

Improving sub select query on two tables without relationship

I am trying to return the count of employees by salary band and can return data using the following sql but wondered if this was the most efficient way of achieving it? I know creating a join ...
1
vote
0answers
41 views

Bulding a “LIKE” query in Yii2

This code is making an SQL query. The last andWhere() method adds a LIKE condition with one percentage sign at the right. I'm ...
5
votes
2answers
25 views

Out of one Oracle into another

A project that I have been working on required data to be transferred from an Oracle database into MySQL. The process I devised for that transfer involved a query (included below) on the Oracle ...
1
vote
1answer
21 views

Building query for search engine

I'm writing a basic search engine for my website. It works but I'm really unhappy with the code. I know it can't be improved but I'm lacking some experiences here. In my params from my search I get: ...
10
votes
3answers
209 views

Trading Card Game database schema for statistics

This database schema will be used in conjunction with various systems as related to an online Trading Card Game, mostly for permanent storage and statistics. Some example cases would include: JDBC ...
2
votes
1answer
30 views

Better way to divide by the total count SQL

I have this query where I am working out the percentage of peoples occupation, I have a sub query that works out the total count of people codes and that result is what I used to divide by, I could ...
4
votes
1answer
22 views

Grouping counts from tables by account ID

I'm working with this query to get counts from three different tables, and to group the results by account ID: ...
20
votes
2answers
1k views

Calculate amount of mugs StackExchange should give away so I'll receive one

Description Calculate how many mugs StackExchange should give away(for graduation) to top reputed users on codereview.stackexchange.com so I'll also receive one. (output is divisible by 50). Code ...
3
votes
1answer
21 views

Change Data Capture using HIVE

Could you please review my code and let me know if there is something wrong? I'm getting the expected results. I'm trying to do change data capture using hive. We already have an existing CDC in ...
2
votes
1answer
15 views

Sorting of nested categories inside a Common Table Expression

I have a PostgreSQL database which contains (among other things) the following table definition: ...
7
votes
1answer
69 views

Filter out date records which don't fall within or span a date range

I have records which have specific time spans. The need is to select records which adhere to the following three rules (read them as or conditions) concerning a ...
11
votes
2answers
95 views

SQL-Server Merge Statement with .NET DataTable

Background: I'm trying to synchronize order information between Oracle and SQL-Server databases and allow users to be able to track any changes between what has been replicated via an ASP.NET web ...
2
votes
2answers
33 views

Assign Specific Value If BitField Is True

My colleague created this SQL to handle certain user selections which are based on an on/off bit flag. The goal is to identify those individual bit flags with a specific integer which will be used in ...
2
votes
1answer
44 views

CAPTCHA with images of objects

This CAPTCHA is not text but basically images of objects that the users has to identify between an array of options. My current code is kind-of slow because the query takes almost .7s to execute and ...
5
votes
1answer
114 views

What are the latest Odds?

The main problem the following query is solving, is the following: Each Offer has multiple Odds (...
3
votes
2answers
40 views

Extract last record inserted for each key identifier

I have a table with this schema ID, int primary key PathKey, string not null InsertDate, datetime not null Value, int not null In the table there are a ...
0
votes
1answer
50 views

Correct MySQL DB Query and processing

I ve got a table called EXP it looks like this The table contains REG_ID - (id of the registred users) TRA_TYPE - (type of transportation the user chose 1-export 2-import) PRO_ID - (product id ...
3
votes
2answers
74 views

Finding the runtime of a task by name

Can this query be improved? I iterate over a couple of thousand times, and if I can improve it, it should decrease the run time. Otherwise I might look into using a batch instead of doing one at a ...
1
vote
2answers
105 views

Weekly fee calculation

I have the following query. Its execution took 15 min and it's too much slow. Is there a way to optimize it? ...
7
votes
3answers
246 views

Normalizing Invoices

I need to move data from one database to another, and since I don't have SSIS I'm doing this ETL with T-SQL scripts. One of the source tables contains invoice details, and features a column that ...
-2
votes
3answers
63 views

More intuitive formatting for a SQL insert formed with PHP

I wish to know how to make this code (and similar) more intuitive: ...
3
votes
2answers
97 views

Role-based access control query

I use SQL Server, and though not pertinent here, Railo's CFML engine. I'm not great at SQL Joins, but I worked through this one and got the result set that I want. This particular SQL will only ever ...
4
votes
1answer
64 views

High school social network query for friends in common

This is sourced from the Stanford Coursera self study DB class SQL quizzes: Students at your hometown high school have decided to organize their social network using databases. So far, they have ...
4
votes
1answer
41 views

Saving and updating records

I have made two SQL statements as I was unable to figure out how to solve my problem. One is called Product_GetID and the other ...
2
votes
0answers
44 views

Merging totals from rows with duplicate IDs

I have the following query which updates the quantity when there are duplicate items and users (there should only ever be 2 duplicates), then deletes the row which doesn't get updated... ...
0
votes
2answers
166 views

Optimize query containing too many inner join and rows

I have the following query: ...
6
votes
2answers
149 views

Follow up to Top Active Answerers on Stack Exchange site

This is a follow up to Top Active Answerers on a Stack Exchange site Actual SEDE Query --> Top Active Answerers on site Do I have a good understanding of the ...
3
votes
2answers
268 views

Top Active Answerers on a Stack Exchange site

I created this query back in January to find the top active answerers on Code Review and thought that it might be a good idea to get other opinions on my SQL coding using a database that we can all ...
2
votes
1answer
33 views

Query of attributes of mobile phone models, using several UNIONs to find distinct values

I'd like to know if there's any room to improve the following query which is to find distinct values from multiple tables when someone searches for a model name. Two of the tables are storing phones' ...
3
votes
1answer
27 views

SQL update statement

I have a SQL update statement which does what it's supposed to. However, I have a feeling that the way I wrote it isn't the best. I am not highly experienced in SQL so any pointers or hints on how can ...
7
votes
2answers
128 views

SQL service broker and threading

Basically my code waits for database table a to have an XML string inserted into it. The SQL query parses the XML string and stores it into separate columns (this ...
1
vote
1answer
21 views

MySQL proliferation of subqueries and left joins

I have the following query: ...
2
votes
3answers
215 views

Rewriting SQL IN statement with long list of parameters

These IDs seem like they should be a table as, there are several queries that use the same list of IDs. The following needs to be refactored. ...
7
votes
3answers
103 views

Step 2: Creating functions the business tool will use

I've been working on this project and here is my first function for step 2. I feel this is the most important one, as I will carry over recommendations from this ...
1
vote
1answer
27 views

Weighting mysql full-text search results by date

I'm using innodb full-text search to return a set of results where the newest posts are more relevant. Just wanted to check that this the best method: ...
6
votes
1answer
77 views

Database of students in a social network

This is sourced from the Stanford Coursera self study DB class SQL quizzes. Students at your hometown high school have decided to organize their social network using databases. So far, they have ...
5
votes
2answers
82 views

Average of averages

I need to get the average of multiple averages calculated basing on data contained on a sql database. To do that, I'm using this code: ...
1
vote
2answers
93 views

Chat server in Python

I just need to know if it's decent or not. Project home ...
5
votes
2answers
34 views

SQL Group and Filter - Refining down a search including Dates

The below process is designed to pick out only AsbestosUPRNs and get the lowest OverallRiskCategory where they have the newest SurveyDate. I just wanted to ensure this is the best way, it seems neat ...
6
votes
1answer
41 views

Query for finding possible “Not An Answer” posts

I have this query in SEDE to try to find possible not an answers to flag. (I already went through a lot of them, for Stack Overflow, so I don't really recommend anyone else using this one since isn't ...
4
votes
2answers
146 views

IMDB website query to find actors by time period

I'm using data from a database from the IMDB website. The database consists of five relevant tables. Actor (id, fname, lname, gender) Movie (id, name, year, rank) Director (id, fname, lname) Cast ...
4
votes
1answer
146 views

MySQL IMDB Query Speed

I'm using data from a database from the IMDB website. The database consists of five relevant tables. Actor (id, fname, lname, gender) Movie (id, name, year, rank) Director (id, fname, lname) Cast ...
2
votes
2answers
156 views

Does this column need to be indexed?

I have the following query which brings back a list of items which fall into a particular category: ...
3
votes
3answers
199 views

SQL delivery query performance

I need a "little" help with an SQL query (MySQL). I have the following tables: COURIERS table: ...
7
votes
2answers
91 views

Query for your Answer Progress

I was inspired to write a query that produced a graph mapping the perceived quality of your Stack Exchange answers over time.... in other words, "Are your answers getting better, or worse?" Because ...
5
votes
2answers
65 views

Cron job that runs a few checks across different tables in database

I have been working on a cronjob that runs a few checks across different tables in my database. The issue is that although it works, I feel like I am doing something horribly wrong in terms of the ...