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.

Find 3 latest posts

5
Hi
I don't know how to discribe my problem in sql language, as I'm new to this :-)
but here is my problem

database test

animal---city-------place---number----time

cow-----odense-----er--------45----------9:00
horse---odense----er---------56----------9:10
hens----odense----gt---------51----------9:20
pig-------odense----ht---------49----------9:30
cow-----odense----er---------50----------9:35
cow-----odense----er---------57-----------9:40
cow-----nowhere---er--------49-----------9:40

I would like to get 3 rows with the lowest number from the city odense
that would be

cow-----odense-----er--------45---------9:00
pig------odense-----ht--------49----------9:30
cow-----odense----er---------50---------9:35
Expand|Select|Wrap|Line Numbers
  1. SELECT  animal, city, place, number, time
  2. FROM test
  3. Where city = 'odense'
  4. ORDER BY number
  5. LIMIT 0,3";
  6.  
But now I have a problem because

cow---------odense---------er---------45----------9:00
cow---------odense---------er---------57----------9:40

are the same animal except number and time are different
then I would like to always use the latest time if an animal have a lot of posts in the database

So I would like to use this row
cow--------odense---------er---------57--------9:40

and ignore this in the query

cow--------odense---------er----------45--------9:00
cow--------odense---------er----------50--------9:35

because there is a newer posts in the database for the same animal

So the result I want is this

horse-------odense----er------56---------9:10
hens--------odense----gt------51---------9:20
pig-----------odense----ht------49---------9:30

I can not find out how to do it, and any help will be appreciated very much.
Aug 1 '08 #1
8 1396
stbdk
5
Oh my I forgot 1 column which is id and it is auto increment
Aug 1 '08 #2
coolsti
310 100+
Maybe this can help you:

http://bytes.com/forum/thread819577.html

You need the query technique that I explain in this thread. The fact that you are selecting data from only one table rather than several joined together makes no difference. You would want to do a "group by" animal since that would eliminate your duplicates, but would not necessarilly give you the row you want back for each animal. So the technique described in the thread I show here will allow you to do that.
Aug 1 '08 #3
stbdk
5
Thank you very much for the answer, I haven't used GROUP BY yet, but I glad you pointed me in that direction :-)

I'm getting closer by using that
now I have
Expand|Select|Wrap|Line Numbers
  1. SELECT id, animal, city, place, number, time FROM test
  2. WHERE city = 'odense'
  3. GROUP BY animal
  4. order by number
  5. limit 0,3
  6.  
which gives
Expand|Select|Wrap|Line Numbers
  1. cow      odense       er      45        9:00
  2. pig       odense     ht       49          9:30
  3. hens    odense     gt       51           9:20
  4.  
How do I now get rid of
cow odense er 45 9:00
I understand the group cow must be filtered by time, so I use the latest post in that group in the query, or that goes for all groups

I'm sorry, but I don't understand this explanation. I'm a newbi to sql :-)
Expand|Select|Wrap|Line Numbers
  1. #
  2. (select C1.id, max(C2.revision) as maxrevision
  3. #
  4. from Companies C1 inner join Locations C2 on C1.id=C2.id group by C1.id) T2
  5. #
  6. on Companies.id=T2.id and revision=maxrevision
  7.  
Aug 1 '08 #4
coolsti
310 100+
I do not have your tables so I cannot check this for syntax errors, but something like this should work:

Expand|Select|Wrap|Line Numbers
  1. select T1.id, T1.animal, T1.city, T1.place, T1. number, T1.time from test T1 inner join (select T2.animal, T2.city, min(T2.number) as minnumber from test T2 group by T2.animal, T2.city) T3 on (T1.animal = T3.animal and T1.number = minnumber and T1.city = T3.city ) having T1.city = 'odense' order by minnumber limit 3
  2.  
There are two parts to this query. The first part defines the columns you would like to see:

Expand|Select|Wrap|Line Numbers
  1. select T1.id, T1.animal, T1.city, T1.place, T1. number, T1.time from test T1 where T1.city = odense order by number
  2.  
but by itself, this will give you all the rows in odense, meaning that if "cow" was there 5 times, it would appear in the results 5 times.

If you just did a group by on this query, you would not necessarilly pull out the one row for each animal that corresponds to the minimum number.

The second part of the query is the creation of a table which I alias as T3:

Expand|Select|Wrap|Line Numbers
  1. (select T2.animal, T2.city, min(T2.number) as minnumber from test T2 group by T2.animal, T2.city) T3
  2.  
This query just gives you the animal, city and the minimum number for each animal, which is gotten due to the group by animal. I include the city here because for example you may have cows in Odense and cows in Århus and you only want to consider the cows in Odense. You would of course need to add a having clause to get only the rows for Odense.

The only thing this part of the query does to help you is to provide a filter for the first part of the query. You join the table T1 with the table T3 and with the "on" clause for the join, you only select the rows from the first part of the query where the animal, the city, and the number match with the animal and the city and the minimum number of the second part of the query. It is confusing at first, but think about it, and you will see what is going on.

Now I may have made some mistakes here. Not being able to test this, I am not 100% sure it works, but the idea is in the right direction, and I hope you at least understand what you need to do.
Aug 1 '08 #5
stbdk
5
First of all thank you very much for teaching me,
ohh year, it works very well, I just changed the min(T2.number) to max(T2.number), and the results are the way I intended.
I'm amased to see four days of my thinking, replaced with 5 lines of a sqlquery :-)

I must admid that I don't understand all of it, I haven't used alias before either :-),
First you say. I want to see these rows:
select T1.id, T1.animal, T1.city, T1.place, T1. number, T1.dato
from test.testdyr T1

Then the group by is a sort of filtering, saying every thing I put into group by, don't show me more than one time,
and the inner join is one more filtering of my data?

I can see I get the same result, if I use where instead of having. Is that the same?

So the idea is to make temporarily tables as many times as you need to get the data filtered?

I don't understand the on part of it.

group by T2.animal, T2.city) T3
on (T1.animal = T3.animal and T1.number = maxnumber and T1.city = T3.city )

Well, this works great, and I have managed to translate it into the real query I'm using, but I have one problem.
First this is the table, (I had to change time in my example into dato as time apperently is a restricted word for mysql).

-- Create schema test
--

CREATE DATABASE IF NOT EXISTS test;
USE test;

--
-- Definition of table `testdyr`
--

DROP TABLE IF EXISTS `testdyr`;
CREATE TABLE `testdyr` (
`id` int(10) unsigned NOT NULL auto_increment,
`animal` varchar(45) character set latin1 NOT NULL,
`city` varchar(45) character set latin1 NOT NULL,
`place` varchar(45) character set latin1 NOT NULL,
`number` varchar(45) character set latin1 NOT NULL,
`dato` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `testdyr`
--

/*!40000 ALTER TABLE `testdyr` DISABLE KEYS */;
INSERT INTO `testdyr` (`id`,`animal`,`city`,`place`,`number`,`dato`) VALUES
(1,'cow','odense','er','45','2008-07-16 09:00:00'),
(2,'horse','odense','er','56','2008-07-16 09:10:00'),
(3,'hens','odense','gt','51','2008-07-16 09:20:00'),
(4,'pig','odense','ht','49','2008-07-16 09:30:00'),
(5,'cow','odense','er','50','2008-07-16 09:35:00'),
(6,'cow','odense','er','57','2008-07-16 09:40:00'),
(7,'cow','nowhere','er','49','2008-07-16 09:40:00');
/*!40000 ALTER TABLE `testdyr` ENABLE KEYS */;



the query:

select T1.id, T1.animal, T1.city, T1.place, T1. number, T1.dato
from test.testdyr T1
inner join (select T2.animal, T2.city, max(T2.number) as maxnumber
from test.testdyr T2
group by T2.animal, T2.city) T3
on (T1.animal = T3.animal and T1.number = maxnumber and T1.city = T3.city )
having T1.city = 'odense'
order by maxnumber
limit 3;



In Denmark we are using another date format and in my other querys I use DATE_FORMAT(dato, '%d-%m%-%Y %k:%i:%s') but I can not get this to work. I have tried a lot of different combinations, but I don't succed. Is there something special going on in this type of querys? :-)
I would like to show the dates like 16-07-2008 09:10:00
Anyway again, thank you very much for your answer. I would never have been able to figur this out by my self right now ;-)
Aug 2 '08 #6
coolsti
310 100+
First of all, I am glad I can help. This query structure I have shown you is a bit complicated, and it would certainly pay for you to try to understand how it is working. You can do this by just running the individual parts of the query, and see how they interact.

I had this very same problem in my work about a year ago (ok, not with farm animals, but another situation) and I had no idea how to solve it. I found the answer on a forum, much like this. I scratched my head and wondered for a long time, how is that query working? But now I understand it, and can extend it to cover other situations.

One thing you need to know is this bit about table alias names. In this query, you are actually creating a temporary table within the query and calling it T3. You can then afterwards refer to this table's attributes with the T3.columnname syntax. You are creating a temporary table of animal - city - max(number) for each animal, and then you are joining this with a table that has all your results (not grouped by) and using the join conditions to select only the rows that give you the animal - city - max(number) - plus other columns that you want. It is because you ALSO want the other columns that you need to do the join and not just use the temporary table T3 that is built in the subquery. You cannot just add these other columns to the table T3 because the group by animal would lose the row association for these columns.

Regarding your time problem, I do not really understand your problem. I live in Denmark too :) and I have had no difficulties using the Date_format function. Try to look at the mysql documentation, perhaps you are using an incorrect letter in the formats, in particular for the time part which I rarely have used myself.
Aug 2 '08 #7
stbdk
5
Okay Thanks a lot for explaining this to me.
Now I have been playing with the query a lot today.
After testing it on the real data, I got suspicious about the data I got back. It was not right, but inserting one more criteria or column in the
inner join (select
and in group by
everything is working perfect again.
Thou regarding the date format,
I'm using this (after several tries) ;-)
SELECT DATE_FORMAT(T1.dato,'%d-%m%-%Y %H:%i:%s')
It doesn't matter if I'm using kis or his in the time format
This works and gives me the right format, but when I run the query in MySQL Query Browser it says
FUNCTION T1.DATE_FORMAT does not exist
Now I'm thinking, is there something I'm doing wrong in the DATE_FORMAT function, or should I just say, what the heck, I don't care, I get the right data, and to live is great :-)
when I use T1.dato there is no error


Wow, now I have been doing some more testing and it doesn't give this error, I don't know what I have changed???
Well, this is a very powerful query, which I don't understand fully right now, but eventually I hope, I will. :-)
I kind of figured you had some relation to Denmark when you wrote Århus instead of nowhere :-)
Again thank you very much for you help, it helped me a lot.
Jeg er ved at lave en hjemmeside mobilbenzin.dk, når den kommer til at køre engang, håber jeg da, du måske kan få glæde af den også ;-)
Aug 2 '08 #8
coolsti
310 100+
Hi,

I think the problem you had is this:

T1.DATE_FORMAT(dato, .....

vs.

DATE_FORMAT(T1.dato, ...

The latter one is correct, the former will give you an error because T1.DATE_FORMAT is probably considered a syntax error.

On the other hand, perhaps you did not actually enter T1.DATE_FORMAT, maybe it is just this mysql browser that is doing that by mistake somehow :)

Glad to have helped! Good luck with the site you are developing!
Aug 2 '08 #9

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

Similar topics

4
by: Gleep | last post by:
Hey Guys, I've got a table called Outcomes. With 3 columns and 15 rows 1st col 2nd col 3rdcol outcome date price There are 15 rows...
0
by: Marcel - IDUG Europe 2005 | last post by:
Visit the blog for a virtual experience of the conference. Check out the blog at: http://idugeurope.blogspot.com Here are our latest posts:
1
by: David Mitchell | last post by:
I have an app being used by 64 users over a network. The back end resides on one of the servers and each user has their own frontend. The latest version has coding to prevent the app being used if...
24
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every...
3
by: Edward Mitchell | last post by:
I am converting a project that uses DirectX and worked under VS.NET 2003. Now when I convert the project to .NET 2005, there are linker errors for _Xran and _Xlen as follows: ...
9
by: Tony Girgenti | last post by:
Hello. I'm developing and testing a web application using VS.NET 2003, VB, .NET Framework 1.1.4322, ASP.NET 1.1.4322 and IIS5.1 on a WIN XP Pro, SP2 computer. I'm using a web form. For a...
14
by: inpuarg | last post by:
I want to find a & character using Regex. But not && How can i manage this in c# Quickfind window ? -------------------------------------------------- ne kadar yaþarsan yaþa sevdiðin kadardýr...
15
by: Kurda Yon | last post by:
Hi, I try to "build" and "install" pysqlite? After I type "python setup.py build" I get a lot of error messages? The first error is "src/ connection.h:33:21: error: sqlite3.h: No such file or...
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...
1
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.