Group By Min, SQL Query

I have a query that is showing multiple results.  I would like to see the minimum sequence number. .  .

SELECT     WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, MIN(SEQUENCE_NO)
FROM         OPERATION
GROUP BY WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, STATUS, SEQUENCE_NO
HAVING      (WORKORDER_TYPE = 'W') AND (STATUS = 'R')
ORDER BY WORKORDER_BASE_ID

This is my table

WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO
100, 1, 1, 10, C
100, 1, 1, 20, R
100, 1, 1, 30, R
100, 1, 1, 40, R

I would like to see this, please. . .but I'm missing something

WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO
100, 1, 1, 20, R

My query results are this. . .
WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO
100, 1, 1, 20, R
100, 1, 1, 30, R
100, 1, 1, 40, R

TIA!
CadenceAerospaceITAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>SELECT     WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID,
MIN(SEQUENCE_NO)

>GROUP BY WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, STATUS, SEQUENCE_NO

Also, the GROUP BY clause should contain all columns in the SELECT that aren't part of an aggregate, such as BASE, LOT, and SPLIT, so what's the deal-i-o with having TYPE and STATUS columns in the GROUP BY clause?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>HAVING      (WORKORDER_TYPE = 'W') AND (STATUS = 'R')
For starters, HAVING is essentially a WHERE clause on an aggregate such as SUM, COUNT, MIN, etc., so what you have here should be in a WHERE clause.

SQL Server GROUP BY Solutions has a couple of demos that would be a good read.
0
 
Simone BConnect With a Mentor Senior E-Commerce AnalystCommented:
You are also grouping by SEQUENCE_NO, although that is in your MIN aggregate. You need to remove SEQUENCE_NO from the GROUP BY. As Jim says, GROUP BY should contain the columns in the SELECT  that aren't part of an aggregate.
0
 
CadenceAerospaceITAuthor Commented:
jimhorn . .

Where throws back a SQL error . .

>HAVING      (WORKORDER_TYPE = 'W') AND (STATUS = 'R')
For starters, HAVING is essentially a WHERE clause on an aggregate such as SUM, COUNT, MIN, etc., so what you have here should be in a WHERE clause.

SELECT     WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, MIN(SEQUENCE_NO)
FROM         OPERATION
GROUP BY WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, STATUS, SEQUENCE_NO
WHERE      WORKORDER_TYPE = 'W' AND STATUS = 'R'
ORDER BY WORKORDER_BASE_ID
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SWEATY - FEET - WILL - GIVE - HORRIBLE - ODORS

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY

Looking at your query, the WHERE needs to be before the GROUP BY.

Good luck getting that out of your head.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.