Postgresql – SQL: group by to select max observation
Imagine you have the following table named city: market product cost A 1 54 A 2 62 A 3 38 B 1 12 B 2 12 B 3 5 I need the most expensive product for each market, i.e.: market…
MySQL SQL EXISTS clause fails as workaround for LIMIT usage restrictions
I was testing the query below thinking it would get me around the "can't use LIMIT in subqueries" restriction. The query runs but the average is simply the average of all the records WHERE MScores.MemberId=1193 AND MScores.Div="FSO" It seems to…
Find records as And in MYSQL
I want find Students id that , must have (professorId = 3 And courseId = 4) And (professorId = 3 And courseId = 2) And (professorId = 5 And courseId = 8). sometime these parenthesis is one or more than…
Mysql – SQL Inner join with where clause works fast with one condition, but not when activeFlag is added
I have a query like select C.customerId, C.firstName, C.LastName, R.IsActive from Customer as C Inner join Rider as R ON R.customerId = C.customerid where R.rideid = 'xyz' and R.rideareacode = 'abc' and R.isActive = 1 This is taking too much…
Mysql – Select user's high score for each lesson from one table based upon lesson ids in another table
I have a site that has multiple courses and each course has multiple lessons. I would like to select the highest score per LESSON for a given user_id and course_id ordered by lesson_order. TABLE lesson id course_id lesson_order ---|-----------|-------------- 1…
Mysql – MergingTwo SQL Queries Select and Count in Single statement
I am Working on project, where I am using two queries to get the result i wanted to process. Query 1 SELECT MID FROM PMS.MACHINE WHERE STATUS=1 AND MID !=0; Query 2 SELECT MID,COUNT(STATUS) AS QUEUE FROM PMS.QUEUE WHERE STATUS=0…
Mysql – Sum values between a lot of date-ranges in a single column
I'm trying to figure out how to get a sum of value by transaction dates over a 90 day period and repeated over a single year's worth of transaction dates. This is what I have transaction_date value 01-01-2024 20 02-01-2024…
How can I set a numeric wildcard in MySQL?
I'm trying to create a SQL script that finds codes in two different databases. I want to set a parameter that has a numeric wildcard. Codes are often written like this for example, ABC108, ABC109, DEF47213, etc. I set the…
Postgresql – Postgres Query to join multiple tables properly
I want to join two tables to get the output as shown in Table4. Below are the details: Table1: SalesData Date PlatformId UnitsSold Revenue ChannelId 2024-07-01 ABCD1 12 1200 1 2024-07-02 ABCD1 11 1000 1 2024-07-01 ABCD2 4 1200 2…
Mysql – IS NOT NULL date in sql or compare with raw data
I want to ask a question, imagine i have this query: SELECT COUNT(DISTINCT (`patient_id`)) AS `num` FROM `visits` AS `Visit` INNER JOIN `patients` AS `patient` ON `Visit`.`patient_id` = `patient`.`id` WHERE `Visit`.`doctor_id` = 21293 AND `Visit`.`office_id` = 14712 AND `Visit`.`started_at` IS…