include select value, but no group by possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

include select value, but no group by possible?

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2006-04-14 : 11:26:18

hi all,

I am using this code below to select the MAX requisition number for the equipment_code.

select max(tool_requisition_number), equipment_code, job_number
from  et_req_history_sum_job_mc
where company_code = 'hwp'
group by equipment_code, job_number
order by equipment_code

This works, but it since I have to group by Job_number because I have it in the select statement it returns TWO values for the equipment_code (with different Job_numbers).

So... for example:
req. # Equip_code job_number
10 e1 job1
8 e2 job4

I only want one to return. The req. # 10 because it's higher. If I removed job_number from the select statement and group by then it works perfect. Though I need to display the job_number too.

When just leaving out job_number from group by I get the "Column 'et_req_history_sum_job_mc.Job_Number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." error.

I do not fully understand this.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-14 : 11:43:44

[code]select max(tool_requisition_number), equipment_code, max(job_number)
from et_req_history_sum_job_mc
where company_code = 'hwp'
group by equipment_code
order by equipment_code[/code]

jubinjose
Starting Member

20 Posts

Posted - 2006-04-14 : 11:51:34

10 e1 job1
8 e2 job4

Both should return right?
coz job code and equipment code are different

btb does table have a primary key?

hueby
Posting Yak Master

127 Posts

Posted - 2006-04-14 : 11:54:03

Hi, that would work, expect my job_numbers do NOT increase with the requisition numbers.

So technically my data could look like:

req#, equip. code, job #

10, e1, dc1111
8, e2, sc6000

So, then the code would pull 10, e1, sc6000

jubinjose
Starting Member

20 Posts

Posted - 2006-04-14 : 11:54:12

I think the sample data u gave is wrong. Anyways

select tool_requisition_number, equipment_code, job_number
from et_req_history_sum_job_mc A
where not exists(select 1 from et_req_history_sum_job_mc B
where B.tool_requisition_number>A.tool_requisition_number
and A.equipment_code = B.equipmentcode)

hueby
Posting Yak Master

127 Posts

Posted - 2006-04-14 : 11:57:27

quote:
Originally posted by jubinjose

10 e1 job1
8 e2 job4

Both should return right?
coz job code and equipment code are different

btb does table have a primary key?

Hi, I actually only want the MAX requisition number to return. 10 e1 job1. I don't care about the lower ones. (This is a history table, that I didn't design, but i'm forced to work with)

Now that I look at my data example, the equipment numbers should be the same. Sorry about that... like:

10 e1 job1
8 e1 job4

I want the highest requisition number for that equipment code, and then the job_number associated with that row.

The job number and requistion number are primary keys.

hueby
Posting Yak Master

127 Posts

Posted - 2006-04-14 : 11:58:22

quote:
Originally posted by jubinjose

I think the sample data u gave is wrong. Anyways

select tool_requisition_number, equipment_code, job_number
from et_req_history_sum_job_mc A
where not exists(select 1 from et_req_history_sum_job_mc B
where B.tool_requisition_number>A.tool_requisition_number
and A.equipment_code = B.equipmentcode)

I did... sorry about that. It should of been something like:

10 e1 job1
8 e1 job4

hueby
Posting Yak Master

127 Posts

Posted - 2006-04-14 : 12:08:20

[code]
select tool_requisition_number, equipment_code, job_number
from et_req_history_sum_job_mc A
where not exists(select 1 from et_req_history_sum_job_mc B
where B.tool_requisition_number>A.tool_requisition_number
and A.equipment_code = B.equipmentcode)[/code]

That gets the job done. THANK YOU. I learned something new...

hueby
Posting Yak Master

127 Posts

Posted - 2006-04-25 : 15:14:02

Thanks for everyone's help...

I've changed the code from > to <, this gives me the correct information now needed.

select tool_requisition_number, equipment_code, job_number
from et_req_history_sum_job_mc A
where not exists(select 1 from et_req_history_sum_job_mc B
where B.tool_requisition_number<A.tool_requisition_number
and A.equipment_code = B.equipment_code)

Only thing is, when a job_number has a single "tool_requisition_number" row, then it does NOT display anything. I'm assuming this is because of the "where B.tool_requisition_number<A.tool_requisition_number" compare?

hueby
Posting Yak Master

127 Posts

Posted - 2006-04-25 : 15:20:24

One second thought, let me explain this:

I went from > to <, because that acutally gave me the higher value. It must be the way the data is formatted in the cell?? It's a VARCHAR(7) and the number will display first then fill up in spaces until 7 characters are maxed.

So: '72 ' and '283 '

With > it took '72 ' instead of '283 '

Is this maybe the problem to address?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-25 : 15:26:26

To get the job_number and don't include it in the group by, you can use a derived table:


select t.tool_requisition_number, t.equipment_code, e.job_number
from 
(
	select max(tool_requisition_number) as tool_requisition_number, equipment_code
	from  et_req_history_sum_job_mc
	where company_code = 'hwp'
	group by equipment_code
) t
INNER JOIN et_req_history_sum_job_mc e
ON e.tool_requisition_number = t.tool_requisition_number AND e.equipment_code = t.equipment_code

Tara Kizer
aka tduggan

jubinjose
Starting Member

20 Posts

Posted - 2006-04-25 : 16:06:17

quote:
Originally posted by hueby

One second thought, let me explain this:

I went from > to <, because that acutally gave me the higher value. It must be the way the data is formatted in the cell?? It's a VARCHAR(7) and the number will display first then fill up in spaces until 7 characters are maxed.

So: '72 ' and '283 '

With > it took '72 ' instead of '283 '

Is this maybe the problem to address?

If you want to comapre the varchar fields as int, then use cast. So your condition would look like
cast(A.jobno as int)>cast(B.jobno as int)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources