Having the classic "posts table, and comments table with foreign key to posts table" scenario, what's the most efficient way to get the IDs of the last 20 posts ordered by the time of their last comment, and the actual comment itself? Here is a query that works but can probably be done much more efficiently: SELECT * FROM ( SELECT *...
I want to extract rows of group by rls_id but with latest/recent date SELECT * FROM `tbl_revisions` where `date` in (SELECT MAX(`date`) FROM `tbl_revisions` group by `rls_id`) group by `rls_id` The above query works well but i dont want to use subqeuries .I need some other way around. CREATE TABLE IF NOT EXISTS `tbl_revisions` ( `i...
If I have three columns in my MySQL table people, say id, name, created where name is a string and created is a timestamp.. what's the appropriate query for a scenario where I have 10 rows and each row has a record with a name. The names could have a unique id, but a similar name none the less. So you can have three Bob's, two Mary's, on...
Lets say I have a table e.g Request No. Type Status --------------------------- 1 New Renewed and then another table Action ID Request No LastUpdated ------------------------------------ 1 1 06-10-2010 2 1 07-14-2010 3 1 09-30-2010 How can I join the secon...
Hello to all! I have a problem with a SQL statement: Using this select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(timestampCol)) as date, a_au.re as RE, a_au.stat as STAT from b_c inner join c on b_c.c_id = c.id inner join a on c.id = a.c_id inner join a_au on a.id = a_au.id inner join revi on a_au.rev = revi.rev where b_...
I need your help building a SQL statement I can't wrap my head around. In a database, I have four tables - files, folders, folders_files and links. I have many files. One of them is called "myFile.txt". I have many folders. "myFile.txt" is in some of them. The first folder it appears in is called "firstFolder". I have many links to m...
Hi everyone, I'm looking for a way to select the most occurring value, e.g. the person who posted most for each thread; SELECT MOST_OCCURRING(user_id) FROM thread_posts GROUP BY thread_id Is there a good way to do this? ...
I have a photos table in the database that uses a status column to detect if the photo was validated or not... so if a photo has a status = 0 then its not validated, but if status = 1 then the photo was validated. I want to add status = 2 for selecting the photo as the 'main photo' so I can use SELECT photo WHERE status = 2 but if t...
Hi ! I have a very simple question for a beginner in SQL. I have defined 3 tables in my database : "DOCTOR", "PATIENTS", "VISITS". For simplicity purposes, I have only a one-to-many relationship btw these tables : One doctor has many patients, but a patient can see only one doctor and one patient can make many visits... in my table "VISI...
In MySQL, I have two tables with a 1:n relationship. Table items has products, whose state is kept in another table, like so : items: id |ref_num|name |... 1 |0001 |product1|... 2 |0002 |product2|... items_states : id|product_id|state_id|date 1 |1 |5 |2010-05-05 10:25:20 2 |1 |9 |2010-05-08 12:3...
I have an Access database that contains a table with information about parts we sort. This table has an autonumber ID field and a 110ID that links to another table with the part information. It also contains a sortDate, sortShift, sorted, scrapped, and repaired. I need to find how many parts have been sorted since the last defect (non...
Every year employee takes mandatory courses. I need to get a list of employees and their latest completed course, for example FS (fire safety). Below is my query. The problem is it shows two or three completed courses for some employees. Trying to troubleshoot those employees’s record, it shows the latest completed course, when I se...
Ok, so the title is a bit convoluted. This is basically a greatest-n-per-group type problem, but I can't for the life of me figure it out. I have a table, user_stats: ------------------+---------+--------------------------------------------------------- id | bigint | not null default nextval('user_stats_id_seq'::regcla...
I have a table named 'Attendance' which is used to record student attendance time in courses. This table has 4 columns, say 'id', 'course_id', 'attendance_time', and 'student_name'. An example of few records in this table is: 23 100 1/1/2010 10:00:00 Tom 24 100 1/1/2010 10:20:00 Bob 25 187 1/2/2010 08:01:01 ...
Let's say I have a query result that looks as follows: ID NAME Phone ---- ---- ----- 1 Bob 111-111-1111 1 Bob 222-222-2222 1 Bob 333-333-3333 2 Stan 555-555-5555 3 Mike 888-888-8888 3 Mike 777-777-7777 I want to return just a single row instance of each ID value. It doesn't matte...
what is the query to return Name and Salary of employee Having Max Salary ...
Hello, I have a bit of SQL that is almost doing what I want it to do. I'm working with three tables, a Users, UserPhoneNumbers and UserPhoneNumberTypes. I'm trying to get a list of users with their phone numbers for an export. The database itself is old and has some integrity issues. My issue is that there should only ever be 1 type of...
I'm trying to select the latest updated (field) pages (table), excluding pages that are hidden (visible = "n"). However, I want to group pages by slug so I only get one result per slug. The following query kinda works, but it only selects the latest updated page for every slug — then, if that page happens to be hidden, it removes it fro...
How can I formulate a query for the below task: Let's say you are logged in as user:1 I want to get one row per conversations I've had. For each row I want to get, the "Subject" of the first row within the conversation "DateTime" of the first row "Message" last message of this conversation no matter who wrote it CREATE TABLE messages ...
I have a table that has the following columns Id ForeignKeyId AttributeName AttributeValue Created Some of the data may look like this: 1, 1, 'EmailPreference', 'Text', 1/1/2010 2, 1, 'EmailPreference', 'Html', 1/3/2010 3, 1, 'EmailPreference', 'Text', 1/10/2010 4, 2, 'EmailPreference', 'Text', 1/2/2010 5, 2, 'EmailPreference', 'Htm...