SQL Query Help - SQL Server Forums

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)

SQL Query Help

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2004-07-22 : 10:03:46

I have two tables in the below format...

Table1

ProcId|LotId|TravLotId
________________________
1001| 1| 101
1002| 1| 102
1003| 1| 103
1004| 1| 104
1005| 2| 210
1006| 2| 220
1007| 2| 240
1008| 3| 45
1009| 3| 46

Table2

ProcessId|ProcNum
______________
1001 | 5
1002 | 10
1003 | 3
1004 | 30
1005 | 500
1006 | 23
1007 | 400
1008 | 100
1009 | 90

How do i write a query to get the following output:

LotId|TravLotId|ProcNum
________________________
1 | 103| 3
2 | 220| 23
3 | 46| 90

I mean i want to get the lowest ProcNum with in a group of Lot id's

Thanks in advance...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-22 : 10:06:23

???? Didn't I already answer this question? Is this a homework question?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dupati1
Posting Yak Master

123 Posts

Posted - 2004-07-22 : 10:28:03

Nope...I came across this kind of a table structure and was wondering how do i get the desrired result...

I tried something like this:

SELECT t1.LotID, t1.TravLotID,t2.ProcNum
FROM table1 t1
INNER JOIN table2 t2
ON
t2.ProcessId = t1.ProcId
WHERE t2.ProcNum In (select top 1 t2.ProcNum from dbo.Process where t2.ProcessId = t1.ProcId ORDER BY t2.ProcNum Asc);

But didnt work....

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 10:35:09

[code]
SELECT A.LotID, A.TravLotId,MIN(B.ProcNum)
FROM Table1 A INNER JOIN Table2 B ON A.ProcID=B.ProcessID
GROUP BY LotID, TravLotID
[/code]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-22 : 10:45:17

Well, that's funny because someone asked this EXACT same question earler on another thread.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37769

Isn't that just the oddest of all coincidences that two people would ask the EXACT SAME questions and it's not related to homework????

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 10:51:04

LOL, and having read that thread, and seen all the info missed from the original question above, my solution won't work !
Definitely homework

dupati1
Posting Yak Master

123 Posts

Posted - 2004-07-22 : 11:06:28

Derrick,

I came across this kind of post on some other forum and was trying to write a SQL query ( i am trying to learn)...

Thanks for the solution..

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-22 : 11:36:26

You need to spell that out in your posts. :) I don't mind spending time on here helping people. I don't want to help people cheat on homework assignments though. I hope you understand that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources