December 18, 2014 at 11:50 am
Hi all,
I am wondering if it is possible to make 1 query that gives several, different resultsets.
I am working on a database for speedskating times, in particular I am working on seasonlists
The query I use for this i (in simplified form)
SELECT LastName, FirstName, Country, Time
FROM TbExample
WHERE Distance = '500' and Gender = 'Man'
My question is about the "WHERE Distance = '500' and Gender = 'Man' " part.
I need the combinations 500-Man, 1000-Man, 1500-Man, 500-Women, 1000-Women, 1500-Women.
In the current setup I need to make 6 queries.
Is it possible to make some kind of (as they call it in Excel) Array (or 2: 1 for distances, 1 for gender) that runs through all posiible combinations?
BTW: using the term array is just an assumption on my part
Thanks in advance
Hein
December 18, 2014 at 12:35 pm
Please provide sample rows and sample output. I would think at a minimum you would need to include the gender and distance columns so you can specify what the other values represent. This should be a trivial query if I am understanding your requirements (which will become crystal clear with sample/output).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 18, 2014 at 1:00 pm
Hi Kevin
Thanks for your reply
I have made an exmaple for you, containing the 6 different query results.
Hope this helps you to get an idea
Hein
December 18, 2014 at 1:26 pm
Can't you just do something like this:
SELECT IceRink,Country_IceRink, Name, Gender, Country_Persons, Time, Distance
FROM TbExample
WHERE (Distance = '500' and Gender = 'Man' )
OR (Distance = '1000' and Gender = 'Man' )
OR (Distance = '500' and Gender = 'Female' )
... other permutations
NOTEs:
a) I hope distance really isn't a string
b) you may wish to have an order by of some flavor
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 18, 2014 at 1:35 pm
Hi Kevin
I have already, in an earlier stage looked at tyour suggestion.
In my query I ask for the top 100 results in a database that holds up to 10 million records.
Using a select query that uses mentioned Where clause would only give me the top 100 results of the men 500 m (by far the fastest combination)
Without knowing if it is possible I was thinking about creating 2 arrays:
1: arrDistance(500, 1000, 1500)
2: arrGender(Male. female)
In the execution phase (or how to call it) the arrDistance (500) gets combined with both male and female
After that the 1000 gets combined to male and female and as last combinations 1500 - male and 1500 - female
Am I thinking way out of line, or is there a way to get this working?
Hein
December 18, 2014 at 2:20 pm
I will repeat my request for sample data, and it needs to be in the form of a table with rows inserted into it. Give me that and I will provide you with a query to accomplish what you want. I am thinking a pair of CROSS APPLYs could work here to get you the combination of filters you need while still getting you the TOP 100. Oh, do you need an ORDER BY on that TOP??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 18, 2014 at 2:23 pm
The solution posted will do what you want, naturally if you limit the result set to 100 out of millions of possible rows you'll be missing a lot of data.
Do you have any requirements for what kind of distribution you're looking for? For example 10 records from each possible combination or the like?
December 18, 2014 at 6:01 pm
Perhaps this?
WITH Combos (Distance, Gender) AS
(
SELECT '500','Man'
UNION ALL SELECT '1000','Man'
UNION ALL SELECT '1500','Man'
UNION ALL SELECT '500','Women'
UNION ALL SELECT '1000','Women'
UNION ALL SELECT '1500','Women'
)
SELECT LastName, FirstName, Country, Time
FROM TbExample a
JOIN Combos b ON a.Distance = b.Distance AND a.Gender = b.Gender;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 19, 2014 at 5:19 am
If there're no other Genders besides 'Man' and 'Women' then you need only 3 queries under current setup.
I second a hope that Distance is a numeric column. Then just
...WHERE Distance BETWEEN 500 AND 1500
December 19, 2014 at 6:45 am
Dwain, I think the objective is TOP N Per Group. But I am waiting back on definitive confirmation of that from the OP.
Serg, the BETWEEN doesn't seem appropriate because there could be a 1000 distance for instance that the OP didn't want in the output. An IN clause could be doable. But again, I think we need clarification on need before proceeding with recommendations.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 19, 2014 at 7:13 am
Hi all
I see that my thread receives replies from several people, thank you for that.
Kevin sorry for not replying yesterday to your thread, it got kind of late in the Netherlands, that's why.
Maybe a return question: do you need a sql server database/ table with a lot of data in it, or can I collect the data in 1 big Excel sheet with (let's say) 10000 lines?
The last is the most easy, maybe also for you to import the data?
Your other question about sorting: these are speedskating times, so the fastest 1 wins. Therefore I always use Order by Times Asc
The "Between" suggestion gives more results than wanted. There are many different distances, and not al of them are "standard", think about old races where distance are in miles (1.609 meters) or in 1/4 miles etc.
Kevin's guess is correct: I am looking for the top N records in each segment.
BTW the distances I gave/used are meant to be a sample. Beside the 500, 1000 and 1500 there more main-distances like 3000, 5000 and 10000 meters
Besides these distances, sometimes a race track is too long or short, resulting in many different distances (999 m is not 1000m!)
Hein
PS
I have made a new Excel files with 6000 data rows, and I have a SQL Server table with the same data
I am fairly new to this, but how can I upload / attach a table to this thread?
December 19, 2014 at 9:15 am
I have used this sproc for nearly 15 years to generate insert statements for a table of data:
http://vyaskn.tripod.com/code/generate_inserts.txt
We don't want to deal with that much data though. Create sample data of few rows that represent your need. You can explain what you need with samples and outputs with probably just a few tens of rows. Use TOP 2 instead of TOP 100 and it should be doable - that way you only need a row or few more than 2 per group to get your need across. You won't need more than 500 and 1000 either. In almost all cases of programming logic that I can think of off the top of my head going from 1 to 2 provides logic equivalent as going to N.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 19, 2014 at 9:23 am
Hi Kevin
I have added, in my earlier thread, an Excel file with (maybe too much) data.
In my 2nd thread (from yesterday) I have posted an Excel files with the 6 wanted outcomes (6 different rowsets based on the 1 rowset I have posted today)
Is this enough for you to work with?
Hein
PS
Sorry, I had posted the wrong Excel file (. were changed to @)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply