SQL not giving Distinct results on Order By

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 not giving Distinct results on Order By

Author

Topic

meikeric
Starting Member

3 Posts

Posted - 2005-12-08 : 16:24:19

Hello all,
I am writing a small script in PHP.

I have 4 tables
1. prices (id, price, station_id, time, user_id)
2. areas (id, town)
3. stations (id, station_name, address, phone, town, area_id)
4. users (id, name)

My Query is:


SELECT DISTINCT p.station_id, p.id, p.price, p.user_id, s.station_name, s.address, a.town, p.time, u.name FROM prices p, stations s, users u, areas a WHERE p.time > now()-(86*60*60) AND p.station_id=s.id AND p.user_id=u.id AND s.area_id = a.id ORDER BY p.station_id, p.price

However, this is giving me duplicates of the station which I do not want.
I would like it first to order by the station id, then by the price. Then I would like it to take the top pick from each station_id, so that only the lowest price from each station is picked. Does that make sense?

Does anyone have any suggestions as to how to do this? I've been told to use a temporary table. I was hoping to do it without resorting to that. I've not been able to figure out if a Union will do what I need.
Any and all suggestions are greatly appreciated.
Mike

jhermiz

3564 Posts

Posted - 2005-12-08 : 16:44:29

You are getting duplicates because DISTINCT does not work on a column basis, it works for the entire row. What I mean by this is its pulling DISTINCT for each column of that row, so obviously the entire row is not duplicated so it pulls back everyhthing. You need to read about the GROUP BY clause and AGGREGATE functions to correctly get this query.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

meikeric
Starting Member

3 Posts

Posted - 2005-12-08 : 17:40:44

[code]SELECT p.station_id, p.id, p.price, p.user_id, s.station_name, s.address, a.town, p.time, u.name
FROM prices p, stations s, users u, areas a
WHERE p.time > now( ) - ( 86 *60 *60 )
AND p.station_id = s.id
AND p.user_id = u.id
AND s.area_id = a.id
GROUP BY p.station_id
ORDER BY p.price
[/code]
is where I'm at now. This is close, but still no cigar.
This returns the first rows in the group p.station_id. I need it to order that p.station_id group and then return the lowest price in the field.
Any ideas? jhermiz, you do have me scouring the internet. The Aggregate functions I found were SUM, FIRST, LAST, etc. I didn't see any I could use.
Thanks!
Mike

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-09 : 01:11:36

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

meikeric
Starting Member

3 Posts

Posted - 2005-12-09 : 11:04:04

Table: prices
id | station | user_id | time | price
1 | 4 | 62 | 051208152115 | 315
2 | 3 | 62 | 051208152115 | 312
3 | 2 | 62 | 051208152115 | 316
4 | 3 | 62 | 051208152330 | 209
5 | 5 | 62 | 051208152400 | 210
6 | 3 | 62 | 051208172959 | 320
7 | 5 | 62 | 051208172959 | 110
8 | 4 | 62 | 051208173019 | 105
9 | 5 | 62 | 051208173019 | 356

Table: areas
id | town
1 | Nags Head
2 | Kill Devil Hills
3 | Manteo
4 | Kitty Hawk
5 | Currituck
6 | Barco
7 | Coinjock

Table: stations
id | station_name | address | phone | town(erroneous) | town_id
1 | 7-Eleven | 7315 S Virginia Dare Trl | 2524414674 | Nags Head | 1
4 | Chevron | 7309 S Virginia Dare Trl | 2524415169 | Nags Head | 1
2 | Nags Head Shell | 3643 S Croatan Hwy | 2524415195 | Nags Head | 1
3 | 7-Eleven | US Highway 64 And 264 | 2524735685 | Manteo | 3
5 | 7-Eleven | 3868 N Croatan Hwy | 2522614733 | Kitty Hawk | 4

Table: users
id | name
62 | administrator

I need to list/output from lowest to highest the top 5 or 10 gas prices, bearing in mind, I can't have duplicates from each station and I need the most recent price from the station. This is a gas price script, FYI. Ignore the town field in the table stations, as this is being replaced by table: areas. Let me know if this is enough information.
Thanks again!
Mike

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-12-09 : 11:27:55

Are you working in MS Access? Now() isn't a valid SQL Server function.

This should work for SQL Server:

SELECT	TOP 10	p.station_id,
		p.id,
		p.price,
		p.user_id,
		s.station_name,
		s.address,
		a.town,
		p.time,
		u.name

FROM 		prices 
AS		p

JOIN		(
		SELECT		tp.station_id,
				MAX(tp.time) AS MaxTime
		FROM		prices 
		AS		tp
		GROUP BY	tp.station_id
		)
AS		mp
ON		mp.station_id
=		p.station_id
AND		mp.MaxTime
=		p.time

JOIN		stations 
AS		s
ON		p.station_id
=		s.id

JOIN		users
AS		u
ON 		p.user_id
=		u.id

JOIN		areas
AS		a
ON		s.area_id
=		a.id

ORDER BY	p.price

For access, I don't think derived tables work, so I think you'll have to make a query called RecentPriceTime with this code:

SELECT		tp.station_id,
		MAX(tp.time) AS MaxTime
FROM		prices 
AS		tp
GROUP BY	tp.station_id

and then use this:

SELECT	TOP 10	p.station_id,
		p.id,
		p.price,
		p.user_id,
		s.station_name,
		s.address,
		a.town,
		p.time,
		u.name

FROM 		prices 
AS		p

JOIN		RecentPriceTime
AS		mp
ON		mp.station_id
=		p.station_id
AND		mp.MaxTime
=		p.time

JOIN		stations 
AS		s
ON		p.station_id
=		s.id

JOIN		users
AS		u
ON 		p.user_id
=		u.id

JOIN		areas
AS		a
ON		s.area_id
=		a.id

ORDER BY	p.price

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources