Hi Per, Thank you for the additional information. It helps me understand your situation a lot better. Unfortunately, it also raises more questions ;-)
My first comment is that you really ought to have a primary key on your table. From the sound of it, you are new to MySQL at least and possibly to databases in general. Are you familiar with Normalization, which is the central activity of proper database design? I suspect you are not because one of the main results of normalization is the selection of primary keys. Normalization is not particularly difficult to do and I recommend that anyone building a database use normalization in its design, even if it just for a web page that supports one of their hobbies. It looks to me like your 'ettan' table is a completely un-normalized collection of data. If I'm right, then you are going to have lots of problems with queries until you normalize the design properly. Unfortunately, although normalization is not terribly hard to do, it is too involved to teach it to someone in a note in a mailing list. I suggest that you use your favourite search engine and try to find a good tutorial on Data Normalization. Or maybe someone here can suggest one. I looked for a good normalization tutorial a while back but I'm fairly demanding when it comes to tutorials and have never found one that really impressed me. (I just did a Google web search and found over a million hits so you will have lots to choose from! Maybe you can even find a good tutorial in Swedish.) If you have questions about normalization in your chosen tutorial, be sure to post here with specific questions and people will likely be quite helpful. If you and I were to lock ourselves in a room without distraction, I could probably get the information I needed out of you and normalize your data for you in an hour. But I've done quite a bit of normalizing over the years. It's going to take you longer because you've never done it before you and have to learn how to do it first. But I think you will find it well worth it to learn normalization because it will save you a lot of time down the road since your database designs will be much better. I realize that isn't the sort of specific help you had in mind. I would rather have given you a specific answer, like Michael Stassen did, but I think you need to get a proper database design first. I think you'll find that a proper design makes your queries simpler and more obvious. By the way, it was interesting to hear that you have drag racing in Sweden; I'll have to share information with my friends who like car racing. I find it very interesting that these races happen on the streets and with police permission. We have a problem with street racing in this country (Canada). It is very illegal in this country but some individuals and groups have races anyway and there have been several people killed and badly injured in these races. Although there are quite a few formal (and legal!) race tracks in this country, for some reason, many people won't use them; I don't know why. How did you ever persuade your police to permit street racing? What precautions do they take to prevent people being hurt? Rhino ----- Original Message ----- From: "Per Lonnborg" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 30, 2004 2:46 PM Subject: Re: SELECT, ORDER & one DISTINCT kolumn? Hi, Iīll try to tell you a bit more; >It's very hard to answer your questions, particularly the first one, since >you haven't provided the definition of the 'ettan' table (especially the >identity of the primary key) nor shown a sampling of what rows would >typically be in the table. mysql> DESCRIBE ettan; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | stad | varchar(20) | YES | | NULL | | | ort | varchar(20) | YES | | NULL | | | datum | date | YES | | NULL | | | plats | int(3) | YES | | NULL | | | nr | int(3) | YES | | NULL | | | namn | varchar(25) | YES | | NULL | | | fabrikat | varchar(25) | YES | | NULL | | | reakt | decimal(5,3) | YES | | NULL | | | 60fot | decimal(5,3) | YES | | NULL | | | hast | decimal(5,2) | YES | | NULL | | | tid | decimal(5,3) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 11 rows in set (0.00 sec) mysql> As you can see, I have no primary key...I went by the MySQL tutorial creating a database for the first time and I donīt belive they mention the Primary Key there. >Also, you haven't identified what version of MySQL you have. Newer versions >have more capabilities than older ones so the answer to your questions will >depend on the MySQL version. For example, if you are on Version 4.1, we can >suggest subqueries that will help with your requirements but if you are on >V4.0 or lower, we have to suggest other solutions since those versions don't >include subqueries. Oopss! I forgot that. Itīs "4.0.15-log" (precompiled package for FreeBSD) > >The report shown at the link isn't much use all on its own, especially for >those of who don't speak Swedish, since it isn't clear to me how the 'ettan' >table compares to this report. > >Do you mean >that if a driver has *ever* (in his life) run a race faster than 7.5 seconds >he should be ignored from any consideration for the Top 10 list entirely? 7.5 (thats "7 and a half") second is the minimum time a car can be permitted to race on a distance of 1/8 mile. (Yes, we practice dragracing in Sweden!) The reason is that these times are clocked on orinary streets, with permission from the police. If it goes faster than 7.5 seconds, things can go very bad. >Although I don't have enough to answer your questions in detail, I can make >a few general remarks. > >The main technique for ignoring rows that don't meet a condition is the >WHERE statement; if you really want to ignore races where a driver's time >is >over 7.5 seconds, include something like: WHERE race_duration > 7.5 > >GROUP BY may be more useful to you than DISTINCT in this case, especially >if >you are on a version of MySQL that has subqueries because you may need a >subquery to find the fastest time for each driver/car combination. > >Sorry I can't be more help but if you can answer my questions, I think you >will get better answers. I choose to use the 4.0.x-version, since 4.1 still is in beta, and that rules out the subqueries I guess. Maybe you saw the answer from Michael Stassen, I use that query for the moment, and I will look in chapter 3.6.4. I will probably have the other columns displayed as well. Thanks, /Per > >Rhino > >----- Original Message ----- >From: "Per Lonnborg" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Monday, August 30, 2004 7:52 AM >Subject: SELECT, ORDER & one DISTINCT kolumn? > > >Hi, > >Hereīs a question from a newbie: > >I have a database containing ET-times for drivers and their cars. >On the web startpage (http://www.lanechoice.net/~pelle/br/) i have made >a "Top-10" list containing the 10 fastest times. > >I have 2 problems: > >1. How to select just ONE (the fastest of course) time per uniqe driver&car? > >2.IF the driver/car has run some race faster than 7,5 secs, he/she is >disqualified >and should not be on the "Top-10" at all. > >The query I use right now is: > >Select * from ettan where tid >=7.5 ORDER BY tid LIMIT 10 > >/Per >Stockholm, Sweden > > > >_______________________________________________________ >Skicka gratis SMS! >http://www.passagen.se > > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > _______________________________________________________ Skicka gratis SMS! http://www.passagen.se -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
