Subquery with Group By Giving me too many results

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 2005 Forums

Transact-SQL (2005)

Subquery with Group By Giving me too many results

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2009-03-30 : 16:38:13

I am trying to get this down to one item per site sku, but at this point am getting 3 lines per site sku. All three lines being returned are identical, but it still is showing 3 lines per.

Any help would be greatly appreciated.

SELECT sclev.MktLvl3 AS SiteSku, (SELECT TOP 1 sclev2.MktDesc FROM sclev AS sclev2 WHERE sclev2.MktLvl3 = sclev.MktLvl3) AS MktDesc,  (SELECT TOP 1 sclev3.MktLvl1 FROM sclev AS sclev3 WHERE sclev3.MktLvl3 = sclev.MktLvl3) AS MktLvl1,  (SELECT TOP 1 sclev4.MktLvl2 FROM sclev AS sclev4 WHERE sclev4.MktLvl3 = sclev.MktLvl3) AS MktLvl2 FROM sclev WHERE sclev.MktLvl3 <> '' GROUP BY sclev.MktLvl3, MktDesc, MktLvl1, MktLvl2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-30 : 16:58:14

Why are you using SELECT TOP 1? Why aren't you using aggregate functions instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-30 : 16:58:37

Also, show us a data example of what it is returning and what it is supposed to be returning.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dprichard
Yak Posting Veteran

94 Posts

Posted - 2009-03-31 : 07:33:48

My data is like this

Site Sku - MktDesc - MktLvl1 - MtkLvl2

1001 - Shoes - 100 - 300
1001 - Shoes - 200 - 300
1001 - Shoes - 300 - 400
2001 - Purse - 100 - 400
2001 - Purse - 200 - 500

I need to submit this information, but can't have duplicate skus so I am tying to just pull on result per sku. I am submitting the data and it won't matter if all the results are there just one per sku so I would like to show it like this.

1001 - Shoes - 100 - 300
2001 - Purse - 100 - 400

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 08:56:29

[code]
/*
Site Sku - MktDesc - MktLvl1 - MtkLvl2

1001 - Shoes - 100 - 300
1001 - Shoes - 200 - 300
1001 - Shoes - 300 - 400
2001 - Purse - 100 - 400
2001 - Purse - 200 - 500
*/

declare @Yak table(Site_sku int, MktDesc varchar(7), MktLvl1 int, MktLvl2 int)

insert into @Yak (Site_sku, mktDesc, mktlvl1,mktlvl2)
select 1001, 'Shoes',100,300
union all select 1001, 'Shoes',200,300
union all select 1001, 'Shoes',300,400
union all select 2001, 'Purse',100,400
union all select 2001, 'Purse',200, 500

select
z.Site_sku,z.mktdesc,z.mktlvl1,z.mktlvl2

from
(select
q.Site_sku,q.mktdesc,q.mktlvl1,q.mktlvl2
,row_number() OVER (partition by q.site_sku order by q.site_sku) as FID
from
@yak q
) z
where
z.fid = 1
[/code]

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources