473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Grouping results from different rows

HaLo2FrEeEk
404 256MB
I couldn't think of a good title to describe what I want to do.

Basically, I'm tracking challenges for a video game. There are 4 daily challenges and 1 weekly challenge. Sometimes the challenges are reused, so I've got a table called "challenges" where I store them. When I get the new information, I check that the new challenges don't already exist in the challenges table, as they would if they were reused, and if they're not I insert. Each challenge gets a unique ID. I also have 2 other tables, "current_daily" and "current_weekly". Every day after I insert the new challenges (or skip them if they already exist) I insert a new row into the curent_daily table (and if it's sunday, I put a new row in current_weekly too). The current daily table has a date column, 4 columns for the ID's of the current challenges, and an expiration date column. So for example, if I inserted 4 new challenges with IDs 51, 52, 53, and 54, then my current_daily row would look like this:

[today's date] | 51 | 52 | 53 | 54 | [expiration date]

Since these challenges are sometimes reused, I want a simple way to see which day a specific challenge was used on. So again, for example, if today's challenges were the above 51, 52, 53, and 54, and I wanted to see what other days any one of those challenges were used on, what query could I use? The column names for the curent_daily table are date, daily1, daily2, daily3, daily4, and expiration. I used this query:

SELECT * FROM `current_daily` WHERE `daily1` = # OR `daily2` = # OR `daily3` = # OR `daily4` = #

And it does work, I used a test number of 108 and it turned out that that challenge was used on march 7th, and march 14th (today). The only problem with is that there are 4 challenges every day, and I'd like the page that displays them to have a little "other days this challenge was used" section under each one, which means I'd have to run that query 4 times. It seems like it might be kinda resource intensive, but maybe that's just me.

So, will this query work, or is there a better one I should be using?

Edit: As an aside, would there be an easy way to calculate the challenge that's been reused the most? Without having to put each one through that query. The only way I could think to do it would be to run each id in the challenges table through that query and count the results. That would be WAY to resource intensive, especially when the challenges table gets bigger.
Mar 15 '11 #1
6 1870
Rabbit
12,516 Expert Mod 8TB
The table layout really should be ChallengeDate, ChallengeID, ExpirationDate to normalize the data and make querying easier. But you could unpivot the data by unioning the individual columns together into one column.
Mar 15 '11 #2
HaLo2FrEeEk
404 256MB
So basically one day's worth of challenges would simply have 4 rows, each with the same date and expiration, but the appropriate challenge id? Seems like it'd be a waste of space duplicating the date and expiration like that.

I'm not sure what you mean by unioning the columns together, can you explain that? I've used MySQL for years but most of it is simple SELECT, UPDATE, INSERT queries, never really had a need for anything more.
Mar 15 '11 #3
dgreenhouse
250 Expert 100+
As Rabbit said, you should really normalize your tables.
The current structure makes it hard to grow.
What happens if you decide to add a 5th daily challenge?
By normalizing the main table, you'd probably no longer need the daily and weekly tables.

By the way... "Unioning" the table(s) would be something like the following:

Expand|Select|Wrap|Line Numbers
  1. select field1, field2, field3 
  2.   from table1 where condition1
  3. union 
  4. select field1, field2, field3 
  5.   from table1 where condition2
  6.  
Mar 15 '11 #4
HaLo2FrEeEk
404 256MB
The data isn't controlled by me, but rather by the company that made the game (Bungie, the game is Halo: Reach). I can guarantee with 100% certainty that there will never be a 5th daily challenge, they've said that multiple times. The reason I separated the daily and weekly challenges was so that I wasn't duplicating data, and so that I could query them separately, whether for inserting or selecting.

But let me get this straight, what Rabbit was suggesting is that instead of having a single row like this:

[date] | 0 | 1 | 2 | 3 | [expire]

I should have 4 rows, like this:

[date] | 0 | [expire]
[date] | 1 | [expire]
[date] | 2 | [expire]
[date] | 3 | [expire]

Where the date and expiration would all be the same? Then to get out today's challenges I could do a query like this:

SELECT * FROM current_daily WHERE date = (SELECT max(date) FROM current_daily)

Right? That would make it easier to query for recycled challenges, but no matter what I'd still have to run the query 4 times, once for each of the 4 daily challenges. If I do change it to that though, I still can't think of how I would determine which challenge has been used the most without putting each of the ids in the challenges table through the query. How can I accomplish this?
Mar 15 '11 #5
Rabbit
12,516 Expert Mod 8TB
You just need to take that query one subquery deeper.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM current_daily
  2. WHERE ChallengeID IN 
  3.    (SELECT ChallengeID FROM current_daily
  4.     WHERE Date = 
  5.        (SELECT MAX(Date) FROM current_daily)
  6.    )
Mar 15 '11 #6
HaLo2FrEeEk
404 256MB
I tell ya what I'll do then, I'll create a second, test table, using the existing data and I'll mess with it, to see what's easier. Thanks for the suggestions.
Mar 16 '11 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Francesco Moi | last post by:
Hi. I've got this piece of code: ----------------//--------------------- <html><head> <style type="text/css"> #block {margin:0 0 0 0; display:block;} ..left-pic {display:block; float:left;}...
2
by: Dave | last post by:
I'm getting different results when I display a value in the Output window as opposed to the Immediate window. Why? Code to recreate problem: 'Purpose: Get different results from Output...
0
by: debjanidutta090483 | last post by:
How different forecolor is given in different rows of a MSFlexGrid in VB6 during runtime?
1
by: sudheerk | last post by:
How to split the query results into different rows
4
by: VSS | last post by:
Hello, Under the 'Current Status' footer i have the following control source: =Count(*) This tells me the sub total of rows under each 'current status' grouping. Under the Assigned team...
4
bugboy
by: bugboy | last post by:
I have a foreign key table for defining many to many relationships in two other tables. This table (MapTable) has two foreign keys, aID and bID. MapTable aID | bID I start the query knowing 2...
2
by: idioteque | last post by:
If I have the following table: Client# ....... Value 24 ................ A 24 ................ G 24 ................ F 25 ................ A 25 ...
4
by: CGatto | last post by:
Hello, Just wondering if anyone has ever managed to find a way to have a datagridviewimagecolumn display different images on different rows depending on some data element in the row. Our...
3
by: kevenj | last post by:
Hello, I'm a bit of a newcomer to PHP and am having an issue I can't overcome. I've read some great posts on the issue of grouping results on this forum 1 and can happily display results under...
4
by: user1980 | last post by:
Hello there I have a strange issue and just wanted to know if it is possible in SQL 2005. Can somebody please guide me on this. I have a column with data like 223 245 356 223 356 222 223...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.