Alternative to max query?
Here is the database I'm using: https://drive.google.com/file/d/1ArJekOQpal0JFIr1h3NXYcFVngnCNUxg/view?usp=sharing
I'm trying to find out how I could return the research interest(descrip) with the largest number of interested academics(acnum) without the use of MAX query or ouputting descending order.
Each academic has a unique acnum, so I'm trying to link it with research interest(descrip) without having duplicate acnums.
I tried this:
SELECT x.descrip, x.name_count FROM (select u.descrip, count(*) as name_count, rank() over (order by count(*) desc) as rank FROM interest u WHERE u.descrip IS NOT NULL GROUP BY u.descrip) x WHERE x.rank = 1;
It partly works, but the acnums are duplicates, I want it to count distinct acnums.
See also questions close to this topic
Count Case Statement - When One Field Greater Than Another
I'm trying to determine how pervasive a particular mistake is in my database. I'm comparing one field against another, and when that field is greater then the other, I want it to count it. I'm also grouping it by a different statement. The purpose of this query is to determine where there are cases in my data base when one price field is larger then another.
The part of the query that is causing problems is "COUNT(CASE when p.IMAP > p.MSRP = 1 ELSE NULL END)" in the select statement. I put two little stars around it, hoping that'd help highlight where it is.
select b.brandName, b.BrandCode, p.ProductVendorStockNumber, **COUNT(Case When p.IMAP > p.MSRP = 1 ELSE NULL END) as 'Count'** from products p join brands b on p.brandID = b.brandID where b.assignedTo = 'Steve' and p.IMAP > p.MSRP and status = 1 GROUP BY b.BrandName
Search in a table of three columns where search criteria may be just one column or two column or three column?
I have a database table contains email, code, name. I have a view search page which can take just email as input or just code or just name or email and name or email and code or email, name, code or nothing. So whichever the search criteria is I need to return related rows from the database. How can I manage my query? Any suggestion? Is there any best practice?
Note: Actually I have three tables and from there I get those data by join query.
Index a mysql table (grid search)
I have a mysql table
my_table, which has the following column:
start_cell, end_cell, field_1, field_2, field_3 ..., field_n
Most of the time, I want to do something like:
select * from my_table where start_cell = 'cell_1234' and end_cell = 'cell_5678'
end_cellare both from
I did the following index to speed up the search:
alter table my_table add index(start_cell)
This works fine so far. However, I am wondering if there is any better index approach for this problem? Thanks!
SQL query question from someone with no SQL training
I'm very new to SQL and have what I hope is a simple question. At the moment this query returns the same value for [quantity] if a [Lot No_] is in multiple [Bin codes]. I would like this query to sum the quantity per [bin code],
It seems like the query sums up all entries in the warehouse table and returns that same quantity value for all the entries in the new table.
i.e currently if warehouse table has;
[Quantity] [Bin code] [Lot No_] 40 A x -35 A x 15 B x -15 B x 5 C x 5 C x
It gives the following table (when added with master$bin table)
[Quantity] [Bincode] [Lot No_] [Item No] ------------------------------------------- 30 A X f1 30 B X f2 30 C X f3
Would like it to give the following;
[Quantity] [Bincode] [Lot No_] [Item No] ------------------------------------------- 05 A X f1 15 B X f2 10 C X f3
Thanks in advance, this is the query;
ALTER PROCEDURE [dbo].[sp_QV_Lots] AS TRUNCATE TABLE [QV_Lots]; INSERT INTO [QV lots] --select quantity, [Lot No_], [Bin Code], [Item No] from [QV Lots] SELECT SUM(Warehouse.Quantity) AS Quantity, BinContent.[Lot No_], BinContent.[Bin Code], Bincontent.[Item No_] FROM [DB01].[LiveNLic].[dbo].[Master$Bin Content] AS BinContent INNER JOIN [DB01].[LiveNLic].dbo.[Warehouse Entry] AS Warehouse ON Warehouse.[Lot No_] = BinContent.[Lot No_] WHERE (BinContent.[Location Code] = 'A' OR BinContent.[Location Code] = 'B') GROUP BY BinContent.[Lot No_], BinContent.[Bin Code], Bincontent.[Item No_]
Force existing rows to be unique
Is there a way to force existing rows to be unique on a column before adding a unique constraint? I am adding this constraint to my db:
CREATE UNIQUE INDEX customfields_name_org_id_key ON CustomFields(name, org_id) WHERE deleted IS false;
But would like to first find all cases where this constraint wouldn't be met, and add 1 to the name of one of the rows (recursively adding more 1s if more than two columns that collide). Is there a way to do this in POstgresql?
I am getting a java.sql.sqlexception: Invalid column index error using a CallableStatement
I am using an insert query for a CallableStatement. This is my query as a String:
String strInsert = "INSERT INTO this_table (loc_nbr, rpt_nbr, rpt_type, office_code, date_submitted, rpt_seq, alcohol, drugs, cyber, priority) VALUES (?, ?, ?, 'HOME', sysdate, ?, ?, ?, ?, ?);";
The CallableStatement I am using is:
CallableStatement cs = Connection.prepareCall(strInsert); cs.setString(1, "'" + strLocationNumber + "'"); cs.setString(2, "'" + strReportNumber + "'"); cs.setString(3, "'" + strReportType + "'"); cs.setString(6, "'" + strReportSeq + "'"); cs.setString(7, "'" + strAlcohol + "'"); cs.setString(8, "'" + strDrugs + "'"); cs.setString(9, "'" + strCyber + "'"); cs.setString(10, "'" + strPriority + "'"); cs.execute();
Upon running this, I get a
java.sql.SQLException: Invalid column index
I am currently using Oracle for the database and I would like to exactly why am I receiving this error.
Can I join two data frames using one column in df1 and one of any values in a cell in df2?
I'm working with some geospatial data,
df_geoand am have a CSV of values I'd like to join to the location data frame, called
My issue, however, is that there are multiple ways to spell the values in the column I'd like to join the two data frames on (region names). Look at the Catalonia example below, in
df_geo: there are 6 different ways to spell the region name, depending on the language.
My question is this: if the row is named "Catalonia" in
df_data, how would I go about joining
Difference between two identical tables in mysql
I've two tables that are defined exactly the same but have different number of rows. There are five fields that are partial keys of the tables(which is the primary key for both the tables). I wanted to find the rows that are in one table but no the other. I tried using the exists as follows but didn't work.
select * from table1 where not exists (select * from table2)
These tables have the exact same create statement but have difference in the number of rows. I don't know if it is possible to find the difference by using joins. Thanks!
SQLite How to Join to an extra Table
In this SQLite example, I am selecting rows from tables a, b, and c using the common column 'aa'.
SELECT a.aa, a.ab, a.ac, b.ba, b.bb, b.bc, c.ca, c.cb, c.cc FROM a INNER JOIN b ON b.ba = a.aa INNER JOIN c ON c.ca = a.aa WHERE a.ab = 'blahblah'
This works OK. Now, I need to add an extra table and an extra JOIN. Table 'd' has a column 'd.dc' that is common with table 'c' and its column 'c.cc'. When the correct row is selected in 'c', I want to be able to read the data in 'd.dd'.
SELECT a.aa, a.ab, a.ac, b.ba, b.bb, b.bc, c.ca, c.cb, c.cc, d.dc, d.dd FROM a INNER JOIN b ON b.ba = a.aa INNER JOIN c ON c.ca = a.aa INNER JOIN d ON d.dc = c.cc WHERE a.ab = 'blahblah'
This does not work OK. Please can you tell me how to correct it? I have also tried
FOREIGN KEY (cc) REFERENCES d(dc)
in the table 'c' definition, but it makes no apparent difference.
Here are my table definitions:
CREATE TABLE `a` ( `aa` TEXT PRIMARY KEY UNIQUE, `ab` TEXT, `ac` TEXT ); CREATE TABLE `b` ( `ba` TEXT PRIMARY KEY UNIQUE, `bb` TEXT, `bc` TEXT ); CREATE TABLE `c` ( `ca` TEXT PRIMARY KEY UNIQUE, `cb` TEXT, `cc` TEXT, FOREIGN KEY (cc) REFERENCES d(dc) ); CREATE TABLE `d` ( `dc` TEXT PRIMARY KEY UNIQUE, `dd` TEXT );
The strange results that I got were rather hard to describe, but one thing I noticed was that the only few rows returned were where c.cc were all the same value, whereas in fact there should have many more rows, and c.cc should have had a variety of values.
Oracle procedure to check if value starts from http or https
I'm using IF condition with 'NOT LIKE' in my procedure to check if value starts from HTTP or HTTPS. But if value starts from HTTP/HTTPS or not, then also for both cases it appends base URL.
Need suggestions to fix this.
if lower(l_value) NOT LIKE 'http://%' or lower(l_value) NOT LIKE 'https://%' then l_value := l_baseURL || l_value; end if;
Sql,how to get these results?
Here is the table structure and output i need to need please provided some suggestion.
| requestid | requeststatus | note | lasted updated date | | 2123 | open | copy from requestid 1234 | 2018/8/19 | | 2124 | follow up | copy from requestid 3456 | 2018/8/20 |
how to write a function to get result of requestid which is in the note. for example i need to get 1234(which is request id from there user information is copy) as output.
SQL Server Can you use 'like' on a join?
I have a process where I get data in from one resource (source) and output into a cleaner output file (destination). I use a translation or mapping table (map) to categorize the data from the source before it gets put into the destination. So it is something like this in a process flow:
Source --> Map --> Destination.
My problem is the source file has a field that, at times, contains numerous values separated by a semicolon. For example, the field (called
Type) might contain values
Lemon; Orange; Grape.
In my Map table I only have certain values that I want to map between source and destination. In the above example, I would only want to map the
Orangevalues to the destination. In my Map table I have this:
| Source | Destination | ------------------------- | Lemon | Citrus | | Orange | Citrus |
I have these values separated in the Map because in the source table the Type field can contain only 'Lemon', 'Orange', 'Grape', or multiple values like mentioned above ('Lemon; Orange; Grape').
Is it possible to extract just the
Orangevalues from the source when there are multiple values in the
Typefield through a join or some other way? I do not want to put in every combination from the Type field from my Source table into the Map table and the 'Type' values order changes consistently.
My goal is to be able to get each value from the 'Type' field to map from the source to the proper field in the destination even when there are multiple types.