Query help
-
Could you please help me solve a query problem:
For a given set of StoreGroup(s) or for all StoreGroup(s) find StoreGroups with matching Store.
Create table StoreGroup (
StoreGroup int,
Store int
)
insert into StoreGroup values
(1,1), --
(1,2), --
(1,3),
(2,4),
(2,5),
(2,6),
(3,7),
(3,2), --
(3,8),
(4,1), --
results:
1,1
4,1
1,2
3,2
-
Can you explain a bit further?
-
I think you can get it by Count() Over Partition as follows:
--Creating Table
Create table StoreGroup (
StoreGroup int,
Store int
)
--Inserting Sample Data
insert into StoreGroup values
(1,1),
(1,2),
(1,3),
(2,4),
(2,5),
(2,6),
(3,7),
(3,2),
(3,8),
(4,1)
--Query For Your Requirement
Select StoreGroup, Store From
(Select *, COUNT(*) Over (Partition By Store) as cnt From StoreGroup) As a
Where a.cnt > 1
Order By Store, StoreGroup
Hope it helps.
-
aostanley (5/21/2012)
Could you please help me solve a query problem:For a given set of StoreGroup(s) or for all StoreGroup(s) find StoreGroups with matching Store.
Create table StoreGroup (
StoreGroup int,
Store int
)
insert into StoreGroup values
(1,1), --
(1,2), --
(1,3),
(2,4),
(2,5),
(2,6),
(3,7),
(3,2), --
(3,8),
(4,1), --
results:
1,1
4,1
1,2
3,2
By posting data in a format that you need 2008 or above for, you're cutting out some pretty hefty SQL Ninjas who may not have a 2008 machine accessible to help you with at the time they see your post. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SELECT a.StoreGroup,a.StoreFROM StoreGroup a
WHERE EXISTS (SELECT * FROM StoreGroup b WHERE b.Store=a.Store AND b.StoreGroup<>a.StoreGroup)
ORDER BY a.Store,a.StoreGroup
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Select StoreGroup, Store From StoreGroup
Where Store in (Select Store from StoreGroup group by Store having count(StoreGroup) >1)
Order By Store, StoreGroup
-
Jeff Moden (5/22/2012)
...By posting data in a format that you need 2008 or above for, you're cutting out some pretty hefty SQL Ninjas who may not have a 2008 machine accessible to help you with at the time they see your post. 😉
Cannot believe it! Although Ninjas prefer using very traditional tools (swords, daggers and katanas), they usually have access to all new available technologies... May be you wanted to say SQL karate-kids?
:hehe:
-
Eugene Elutin (5/22/2012)
Jeff Moden (5/22/2012)
...By posting data in a format that you need 2008 or above for, you're cutting out some pretty hefty SQL Ninjas who may not have a 2008 machine accessible to help you with at the time they see your post. 😉
Cannot believe it! Although Ninjas prefer using very traditional tools (swords, daggers and katanas), they usually have access to all new available technologies... May be you wanted to say SQL karate-kids?
:hehe:
And... this is a 2008 forum :w00t:
Just giving you a hard time Jeff 🙂
Jared
CE - Microsoft -
I want to express my appreciation to all of you who replied to my post.
I'm grateful to all of you for taking the time to offer your help.
-
SQLKnowItAll (5/22/2012)
Eugene Elutin (5/22/2012)
Jeff Moden (5/22/2012)
...By posting data in a format that you need 2008 or above for, you're cutting out some pretty hefty SQL Ninjas who may not have a 2008 machine accessible to help you with at the time they see your post. 😉
Cannot believe it! Although Ninjas prefer using very traditional tools (swords, daggers and katanas), they usually have access to all new available technologies... May be you wanted to say SQL karate-kids?
:hehe:
And... this is a 2008 forum :w00t:
Just giving you a hard time Jeff 🙂
Unless you need a feature specific to SQL Server 2008, SQL Server 2005 can still be used to help as well.
Just passing on the same here... :w00t:
-
Eugene Elutin (5/22/2012)
Jeff Moden (5/22/2012)
...By posting data in a format that you need 2008 or above for, you're cutting out some pretty hefty SQL Ninjas who may not have a 2008 machine accessible to help you with at the time they see your post. 😉
Cannot believe it! Although Ninjas prefer using very traditional tools (swords, daggers and katanas), they usually have access to all new available technologies... May be you wanted to say SQL karate-kids?
:hehe:
BWAAA-HAAA!!!! Now, thats funny.
An example of what I mean, though... We're still "stuck" on SQL Server 2005 at work. If I wanted to take a break at lunch and answer a question or two, I wouldn't be able to work with 2008-only data. I'd first have to convert it. I'm just not going to take the time to do that on a lunch break. A whole lot more people fall into this category than you might expect.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (5/25/2012)
An example of what I mean, though... We're still "stuck" on SQL Server 2005 at work. If I wanted to take a break at lunch and answer a question or two, I wouldn't be able to work with 2008-only data. I'd first have to convert it.How on earth do you cope where sample data is provided in text form (as is quite often the case)?
I'm just not going to take the time to do that on a lunch break.
That's fine of course (there are plenty of other questions available), but I hardly think it's right to ask people posting in a 2008 forum to change *their code* for that reason. There are plenty of us running instances compatible with the forum we are in; even where that's not the case, it's not the end of the world to convert row constructors :w00t: to UNION ALL :sick:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
SQL Kiwi (5/26/2012)
Jeff Moden (5/25/2012)
An example of what I mean, though... We're still "stuck" on SQL Server 2005 at work. If I wanted to take a break at lunch and answer a question or two, I wouldn't be able to work with 2008-only data. I'd first have to convert it.How on earth do you cope where sample data is provided in text form (as is quite often the case)?
I'm just not going to take the time to do that on a lunch break.
That's fine of course (there are plenty of other questions available), but I hardly think it's right to ask people posting in a 2008 forum to change *their code* for that reason. There are plenty of us running instances compatible with the forum we are in; even where that's not the case, it's not the end of the world to convert row constructors :w00t: to UNION ALL :sick:
I didn't ask anyone to change their code. I suggested that they're cutting out some people who might otherwise not have the time to do such a conversion. The same holds true for posting data provided in text form.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (5/26/2012)
I didn't ask anyone to change their code. I suggested that they're cutting out some people who might otherwise not have the time to do such a conversion. The same holds true for posting data provided in text form.Fair enough.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply