How do I get records w/same job but diff price

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)

How do I get records w/same job but diff price

Author

Topic

fonzie
Starting Member

31 Posts

Posted - 2008-09-15 : 11:23:58

Can you indicate what is the syntax to get all records that have the same job nbr but diff prices?
Something like this?

SELECT * from l:dlytran2 A WHERE JOBnbr=(select JOBnbr FROM l:dlytran2 B;
WHERE A.JOBnbr=B.JOBnbr AND a.price<>b.price)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 11:42:08

[code]SELECT JobNbr
FROM [l:dlytran2]
GROUP BY JobNbr
HAVING MIN(Price) < MAX(Price)[/code]

E 12°55'05.63"
N 56°04'39.26"

fonzie
Starting Member

31 Posts

Posted - 2008-09-15 : 13:55:24

How would I show each record jobnbr and price in groups?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 14:11:31

quote:
Originally posted by fonzie

How would I show each record jobnbr and price in groups?

may be this

SELECT JobNbr,Price
FROM [l:dlytran2]
WHERE JobNbr IN
(
SELECT		JobNbr
FROM		[l:dlytran2]
GROUP BY	JobNbr
HAVING		MIN(Price) < MAX(Price))

fonzie
Starting Member

31 Posts

Posted - 2008-09-15 : 14:42:24

What does the line "HAVING MIN(Price) < MAX(Price))" do?
return all records less then the greatest price?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 15:13:06

quote:
Originally posted by fonzie

What does the line "HAVING MIN(Price) < MAX(Price))" do?
return all records less then the greatest price?

nope. return all records with min value less than max value (that means at least two different price values existing for same number)

fonzie
Starting Member

31 Posts

Posted - 2008-09-16 : 08:34:29

I would want to see the duplicates next to each other something like this: possible? using group by 2 fields?

J45 90
J77 44
J77 48
J77 66
J83 12
J91 23
J91 27

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 09:20:31

See post made 09/15/2008 : 14:11:31

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources