Need help writing sql query - 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

General SQL Server Forums

New to SQL Server Programming

Need help writing sql query

Author

Topic

jcook6924
Starting Member

6 Posts

Posted - 2010-08-05 : 12:24:33

I've been trying to write this query for a while and have finally swallowed my pride and asking for help.

select distinct BuildingID, TypeID, Date from Unit

ok this is simple, but what im trying to do is display all the columns from this table but only select distinct buildingid, typeid, and date with lowest price.

does this make since? Any help is appreciated.

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 12:27:52

instead of disinct you can just use GROUP BY

i.e.
SELECT a, b, c FROM table_1
GROUP BY a, b, c

jcook6924
Starting Member

6 Posts

Posted - 2010-08-05 : 12:36:19

but that wont let me display all the columns and order by price

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 12:36:43

If you want the min price btw, then this would do it...

SELECT
BuildingID, TypeID, Date, MIN(price)
FROM
Unit
GROUP BY
BuildingID, TypeID, Date

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 12:38:00

So for example, say you have 3 rows with the same BuildingID, TypeID & Date, then this will only reteive the one with the lowest price value.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-05 : 12:38:19

How about


SELECT * FROM Unit o
WHERE EXISTS (
			  SELECT BuildingID, TypeID, [date]
				FROM Unit i 
			   WHERE i.BuildingID = o.BuildingId
			     AND i.TypeID = o.TypeID
				 AND i.[date] = o.[Date]
			GROUP BY BuildingID, TypeID, [Date]
			HAVING o.[Cost] = MAX(i.[Cost])
			  )

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jcook6924
Starting Member

6 Posts

Posted - 2010-08-05 : 12:38:53

quote:
Originally posted by myworldntl

So for example, say you have 3 rows with the same BuildingID, TypeID & Date, then this will only reteive the one with the lowest price value.

Thats exactly what i want but can we include the rest of the columns to display?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-05 : 12:41:05

SELECT BuildingID, TypeID, Date , MIN(price) as LowestPrice
FROM yourTable
GROUP BY BuildingID, TypeID, Date

will give you the lowest price in each BuildingID, TypeID, Date combination


SELECT <all the columns from yourTable>
FROM yourTable yt
INNER JOIN
(
SELECT BuildingID, TypeID, Date , MIN(price) as LowestPrice
FROM yourTable
GROUP BY BuildingID, TypeID, Date 
) t
ON
    yt.BuildingID = t.BuildingID
and yt.TypeID     = t.TypeID
and yt.Date       = t.Date


SELECT t.*
FROM
(
  select <all the columns from yourTable>
       ,[Rank] = rank() over(partition by BuildingID, TypeID, Date order by price asc)
  from yourtable
) t
WHERE RANK = 1

Jim

Everyday I learn something that somebody else already knew

jcook6924
Starting Member

6 Posts

Posted - 2010-08-05 : 12:49:06

quote:
Originally posted by jimf

SELECT BuildingID, TypeID, Date , MIN(price) as LowestPrice
FROM yourTable
GROUP BY BuildingID, TypeID, Date

will give you the lowest price in each BuildingID, TypeID, Date combination

SELECT <all the columns from yourTable>
FROM yourTable yt
INNER JOIN
(
SELECT BuildingID, TypeID, Date , MIN(price) as LowestPrice
FROM yourTable
GROUP BY BuildingID, TypeID, Date
) t
ON
yt.BuildingID = t.BuildingID
and yt.TypeID = t.TypeID
and yt.Date = t.Date

SELECT t.*
FROM
(
select <all the columns from yourTable>
,[Rank] = rank() over(partition by BuildingID, TypeID, Date order by price asc)
from yourtable
) t
WHERE RANK = 1

Jim

Everyday I learn something that somebody else already knew

this returns all rows in the table :(

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 12:50:14

Yse you can include the rest of the columns, just as long as they are not requierd in the group by.

i.e. if you have 2 rows with the same BuildingID, TypeID & Date but then one of your other columns between the 2 rows differs, then you will get 2 rows returned...

If the other columns are all the same however, 1 row will be returned.

The other option is, you want these 2 differing rows returned, but both with the MIN(price) value.
If this is the case, you'd have to create a derived table, join to it, and then bring that back.

An example is below:

SELECT
tab_1.a as tab_1a,
tab_1.b as tab_1b,
tab_1.price as tab_1price,
tab_2.minprice as min_price
FROM
TEST as tab_1,
(
SELECT
a, b, MIN(price) "minprice"
FROM
TEST
GROUP BY a, b
) as tab_2
WHERE tab_1.a = tab_2.a
AND tab_1.b = tab_2.b

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 12:53:57

If you do just purely want the one row returned for each though, then add the other columns into the SELECT clause, and also into the GROUP BY clause, as then you will just get these individual grouped/distinct rows returned...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-05 : 12:56:42

Does your date column have times in it, i.e, '2010-08-05 11:55:53.473' and not just '2010-08-05 00:00:00.000'?

Jim

Everyday I learn something that somebody else already knew

jcook6924
Starting Member

6 Posts

Posted - 2010-08-05 : 12:59:00

[code]SELECT
BuildingID, TypeID, Date, MIN(price)
FROM
Unit
GROUP BY
BuildingID, TypeID, Date[/code]

This works perfectly, but can I at least get the unitID column to display?

jcook6924
Starting Member

6 Posts

Posted - 2010-08-05 : 12:59:40

quote:
Originally posted by jimf

Does your date column have times in it, i.e, '2010-08-05 11:55:53.473' and not just '2010-08-05 00:00:00.000'?

Jim

Everyday I learn something that somebody else already knew

the date column is a int 1-12 (only need to track month).

Might also help if i tell you my business rule: Only display one unit of multiple units with same type and availability(date) with lowest price. (this is per units in a building)

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 13:08:30

Yea sure you can get it to display, just add it to the select and groupn by, i.e:

SELECT
unitID, BuildingID, TypeID, Date, MIN(price)
FROM
Unit
GROUP BY
unitID, BuildingID, TypeID, Date

If you have a group by you need to have all fields in there if they are not part of a sum/aggregate function or something similar.

so price isnt in there as you are using that in the function MIN() but the others you have to place in there as they are not using a function or calculation

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 13:10:47

Where as if you use the previous code i posted

That will display ALL of your columns and ALL of their prices, but also, each of these rows will have the MIN(price) column as well.

col_a col_b price min_price
a a 5 5
a a 12 5
a a 6 5

etc

The code was this which i have reposted below:

SELECT
tab_1.a as tab_1a,
tab_1.b as tab_1b,
tab_1.price as tab_1price,
tab_2.minprice as min_price
FROM
TEST as tab_1,
(
SELECT
a, b, MIN(price) "minprice"
FROM
TEST
GROUP BY a, b
) as tab_2
WHERE tab_1.a = tab_2.a
AND tab_1.b = tab_2.b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources