May 31, 2013 at 12:29 pm
Hello all. I have a simple query question that should be fairly easy to answer for most casual SQL users. Foremost, I would like to thank all those who check and reply to this post. Your help is much appreciated because I am a complete SQL newbie at the moment.
My question...
I have a large table that houses food POS data for one client with many different 'store' locations. These locations are large centers -- stadiums specifically -- with multiple 'stand' locations at each stadium. My dilemma is that I want to find all the 'stand' IDs that are doubled between stadiums. For example... I do not mind finding StandID#'s that are doubled in the scope of 1 stadium, but I want to find the StandID#'s that are shared between Stadiums, because my ultimate goal is to consolidate all these ID's and have unique ID#'s that are not shared between stadiums.
I started out with this query...
SELECT Distinct(StandID), Stadium, FileName FROM [dbo].[Table] ORDER BY StandID
...which gives me the information I need, but it leaves me going through a result set of millions of rows to find the doubles within.
Does anybody have any advice or comments?
Thank you very much
May 31, 2013 at 12:44 pm
reelnoncents (5/31/2013)
Hello all. I have a simple query question that should be fairly easy to answer for most casual SQL users. Foremost, I would like to thank all those who check and reply to this post. Your help is much appreciated because I am a complete SQL newbie at the moment.My question...
I have a large table that houses food POS data for one client with many different 'store' locations. These locations are large centers -- stadiums specifically -- with multiple 'stand' locations at each stadium. My dilemma is that I want to find all the 'stand' IDs that are doubled between stadiums. For example... I do not mind finding StandID#'s that are doubled in the scope of 1 stadium, but I want to find the StandID#'s that are shared between Stadiums, because my ultimate goal is to consolidate all these ID's and have unique ID#'s that are not shared between stadiums.
I started out with this query...
SELECT Distinct(StandID), Stadium, FileName FROM [dbo].[Table] ORDER BY StandID
...which gives me the information I need, but it leaves me going through a result set of millions of rows to find the doubles within.
Does anybody have any advice or comments?
Thank you very much
Distinct is meant to return distinct rows not distinct values in a specific column.
May 31, 2013 at 12:44 pm
You could use a query like this, but you may want to complete it.
This will give you the StandIDs in more than one Stadium. To get the ones that are on just one Stadium you can change the > for an =
At least, that what I figured out with the limited information.
SELECT StandID
FROM [dbo].[Table]
GROUP BY StandID
HAVING COUNT(DISTINCT Stadium) > 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply