473,287 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Complex Query 3 Tables combine ORDER BY + GROUP BY

dlite922
1,584 Expert 1GB
hey fellas (and ladies)

I need help with making a query.

My tables are: (Simplified)

case:
id
violatorID
number

violator:
id
name

trial:
id
caseID
date



A Case can have Multiple trials with different dates.

I need a query that gets returns the following information
caseID, caseNumber, violatorName, and the FIRST trial date.

So i need distinct cases, but it should return the earliest trial date. If there's on in March and one in July, it should return the one in March.

MY ATTEMPT:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT c.id as NUM, c.caseNumber, v.firstName, v.lastName, t.courtDate 
  3. FROM `case` as c, violator as v, trial as t 
  4. WHERE c.violatorID = v.id AND t.CaseID = c.id 
  5. GROUP BY c.id;
  6.  
  7.  
the above returns the last trial date (in above example, the one in July)

Then i tried this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT c.id as NUM, c.caseNumber, v.firstName, v.lastName, t.courtDate 
  3. FROM `case` as c, violator as v, trial as t 
  4. WHERE c.violatorID = v.id AND t.CaseID = c.id
  5. ORDER BY t.courtDate 
  6. GROUP BY c.id;
  7.  
  8.  
  9. //and got...
  10.  
  11. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY c.id' at line 1
  12.  
  13.  
  14.  
can't use Order By and Group By together? at least in this instance i can't.

any tips much appreciate it.

for the record, I know violator should have caseID, instead of vice versa.
Feb 18 '08 #1
1 4876
code green
1,726 Expert 1GB
can't use Order By and Group By together? at least in this instance i can't
No, you've just got them the wrong way around.
GROUP BY has to be before ORDER BY
Feb 18 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: DBNovice | last post by:
I have a database that keeps records on the issue and failure of an item. Currently, the database is poorly desisned; therefore I'm performing queries to break the data into normalized tables and...
1
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes...
14
by: signaturefactory | last post by:
I am trying the following query in and oleDbCommand: SELECT PartLocations.LocationName, Sum(PartsJournal.Quantity) AS SumOfQuantity, PartsJournal.PartsLotNumber FROM PartLocations INNER JOIN...
2
by: Pete | last post by:
Before I get started with the question, does anyone have a (single) good book recommendation for database design? Not an Access-specific book, but something geared toward helping me figure out...
1
by: ravi | last post by:
I have created the following interest to calculate the interest for the following currency pairs. I have tried to combine them in macros using conditions but the next query that is run in the macro...
4
by: ED | last post by:
I am attempting to to write a query that has a numerous nested IIf statements. The problem that I am having is that it is to long of a query to be built in design mode and when I build it in sql...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
0
by: djflow | last post by:
Hi! II was wondering if you can help me with SQL query.. Below 7 separated select query works fine(only when they are retrieved separately) But I want to combined them together and so that i...
2
by: mgsn | last post by:
I Have This this Query --------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER proc . @userId int=null, -- filter: user id. overrides custid when supplied ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.