Query Help: Get Oldest Record W/ Grouping?

Sum Grouping Query Assistance
hours table

hoursID (int)
name (varchar)
hours (int)
hours_status (pending, approved, denied)

I'm trying to develop a query that will output the SUM of each persons hours by status (pending, approved, denied), output would be like:

name - total pending hours - total approved hours - total denied hours
greg - 12 - 34 - 12
maria - 3 - 44 - 4
fred - 5 - 5 - 10

totals for all names - 20 - 83 - 26

View Replies !

---

Count And Delete Oldest Entries
Hi I need help with a query that will cycle through the 'com_for' (userid) field and then only keep the latest 200 comments and delete everything after. So if 'com_for' userid 39 has 250 comments then delete the oldest 50 comments. The com_time field holds the time in this format 1133173361

table: rate_picture_comments
fields: com_for , com_comment , com_time

thanks in advance.

View Replies !

---

Deleting Oldest X Rows Using Primary Key
Lets say i have x number of rows and I want to keep the newest 100 rows using the auto incremented primary key...Can someone help me make a query with a subquery to do this in one shot. I can't seem to get mine working.

DELETE FROM t1 WHERE id < (SELECT id FROM t1 ORDER BY id DESC LIMIT 99,1)

I've also tried this.

DELETE FROM t1 WHERE id not in (SELECT id FROM t1 ORDER BY id DESC limit 1000)

Both do not work.

My goal is to get something like this working so I can run it daily as a cronjob.

View Replies !

---

Delete Oldest Rows Keeping 10 Most Recent, How?
Example: There is a table (NEWS). In that table I want to automatically delete rows so I'll always have 10 rows with the most recent news. The oldest news should be deleted keeping the 10 most recent.

TABLE NEWS is something like ID, news, date. I can't figure out how the query should look like. Any ideas?

View Replies !

---

Forming A Mysql Join Query (with Grouping).
Here goes it:

Lets say I have two tables, 1 named Genres and 1 named Movies.
Genres >>> genre_id, genre
Movies >>> movie_id, movie_name, genre_id, mpaa_rating

When doing a search on movies, I want users to be able to filter out the results by genre. To be more specific, I want all the genres to be listed (as a link) on the left side of the page, with their corresponding counts next to them.

Example:
Action/Adventure [15]
Comedy[7]
Documentary [3]
....etc, where the number is equal to the number of movies in the database that have a matching genre.

I've tried doing this...

View Replies !

---

Grouping For A Query And Limiting Returned Data In Order Of IN()
I'm trying to pull some data from the database in which it's supposed to return 3 rows from each f.forumid in the order in which i have the forumids, however it seems to be returning all the data from the table, what am i doing wrong?

PHP Code:

SELECT t.threadid, t.title, t.lastpost, t.forumid, t.open, t.replycount, t.postusername, t.postuserid, t.lastposter, t.dateline, t.views, t.firstpostid, f.title AS forumtitle, p.pagetext AS preview
    FROM thread t
    INNER JOIN forum f ON (t.forumid = f.forumid) AND t.visible = 1
    LEFT JOIN post p ON(p.postid = t.firstpostid)
    WHERE t.forumid IN(200,250,2,30,15,60,70,90)
    GROUP BY f.title, t.title
    HAVING COUNT(*) <= 3
    ORDER BY f.forumid, t.dateline DESC

View Replies !

---

Query- Get Every X Record?
My app loads my database every 30 seconds with weather data from several different weather stations (2880 records per station per day).

I need to extract every 10th record for a station (288 records per day) to build a PHP array for a graph.

View Replies !

---

Get The First And Last Record From A Query?
I need to return a max, min, open and close in the query but I only have one column which is value. The open would be the first record and the close would be the last record. Is there a way to do this? ...

View Replies !

---

Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:

SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id

View Replies !

---

How To Ommit First Record In Query Result?
How can I ommit the first record of a Query?

View Replies !

---

Return Info On Last Record With One Query
Lets call the table:
comics

Lets call the fields I am looking for:
ID
filename

I want to get the maximum ID from the table and the filename field corresponding to that maximum id.

I can do it easy in two queries:
select max(ID) as max_id from comics
then in php send
select filename from comics where ID=$id
(where $id is the number returned from the first query)

I would like to do ONE query like:

Code:
select max(ID) as max_id,
and filename
from comics
where ID=max_id

View Replies !

---

Insert A Record Using MySQL Query Browser
I know I can probably type in the actual query, but I was wondering if there was a means of inserting a record without typing "insert into table_name (blah, blah2) values (blah, blah)". I'm looking for a method as easy as typing into an Excel spreadsheet or Access DB. (I know I'm gonna get bashed for mentioning Microsoft products.)

View Replies !

---

Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script,
my table having the details of uploading time, file name, & uploader name.

i need to find the recent uploaded file for all uploaders.

i tried with this query,

select file_name,uploded_by,MAX(date_time) from upload group by uploded_by;

it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.

View Replies !

---

How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records
possessing the highest numbered version for that unique file_name. I show
sample data and two trial queries below. Logically I want to use
max(version) as a constraint in a Where Clause. However, the max() function
is not allowed directly in a where clause.

I have contemplated a second table to track the max version for each file
name. I would like to structure the data in an efficient manner for query
performance when the data set grows to many thousands of unique file_name
records with many hundreds of versions each........

View Replies !

---

Query To Retrieve 5 Records Immediately Before And After A Particular Record In Sorted Manner
Just consider a simple table with one integer column (however numbers are not inserted in sorted manner and some of them may be missing).

Given a particular record, say 32, I would like to retrieve 5 records immediately before and after this record in ascending manner of sort.

So, in this case I should get

25, 27, 28, 30, 31, 32, 33, 34, 37, 38, 39 (I did not have 26, 29 and 35 in the table)

Why do I go about writing such query?

View Replies !

---

Automatically Record Date Of Record Entry
I have my database table set up and I have an HTML form that is PHP driven that will add the information entered into the form into to my database table.

I have a local buy-sell-trade Website. The way it has worked is that people fill out a form and the results emailed to me. I then take the information and enter it into a Web page. I only want the ads to be displayed for 30 days. I keep the ad for a total of 6 weeks (displays for 30 days and sits in limbo for 2 weeks afterwords) and if not renewed within that 2 week limbo period - I delete it.

Entering all the ads and keeping up with the dates manually has become a burden. I've only recently began looking into databases. My hosting company provides me with phpmyadmin and mysql 5.0. I'm new to all of this but I have managed to set up a database table and a HTML form that is PHP driven that allows ads to be automatically added to the database table.

There is a lot I need to do to make this ideal, but one step at a time.

First, I need to know the date (March 02, 2006) the ad was created or added to the database table. I know that I need to add some piece of code to my php form to record this information, but what code and where do I put it? I know I will need to create an extra field in my database table to house the date - I can handle this.

I've read the date and time information here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

but there's a lot there and I don't know which is right for my needs. Plus, it doesn't tell me how or where to insert it into my php form (or does it?).

View Replies !

---

Want Only One Record Returned Per Post_id (was "Help With Query")
I can't figure out how to do this query. Help would be muchly appreciated!

I want to get the DISTINCT(post.post_id) but not sure how to do this.

Here is the query I have thus far but it's returning two records each with the same post_id but different cat_id's.

SELECT post.post_id, auth_alias, pc.cat_id, post_heading, DATE_FORMAT(post_created, '%M %d, %Y'), post_body FROM posting post LEFT OUTER JOIN post_cats pc ON post.post_id = pc.post_id WHERE post_status = &#391;' ORDER BY post.post_created DESC LIMIT 0,5

View Replies !

---

Getting Record Before Or After The Record That Meets The Criteria
In a query I want to get all the results and then order those results by last name, but then I want to filter those results down to only the record that comes before or after the record that has 'empno' = '1259'.

I want to get all the results already ordered and then filter them down to one record either before or after (depending on what is needed) the record where 'empno' = '1259'.

View Replies !

---

How To Record The Current Record In A Table?
I'm looking at convert file formats to mysql.

The original file format used an "ENTRY" conception to record the current record.

But I cannot find a solution to emulate "ENTRY" conception in MySQL.

After searching MySQL's tutorials, the AUTO-INCREMENT fields would only use "mysql_insert_id()" to obtain its value. But this value cannot identify the current record, Right? This value are only last record after inserting?

Who can help me to find a better solution to emulate implementing what the "ENTRY" does.

In other words, How to obtain the auto-increment value contains AUTO-INCREMENT columns in a record which generated by SELECT statement.

View Replies !

---

Grouping By A Day
I have a query that groups my order table entries by a month like this:
SELECT month(date_purchased) as month, sum(order_total) as total FROM
`orders` GROUP BY month ORDER BY month

This works fine but now I wanted to group my orders by a day. I can't figure
out how to do this, as there is no day()-function.

The field date_purchased is a datetime field with hours, minutes and seconds
in addition to a date information.

This is what I would like to have but does not work:

SELECT >>DAY(date_purchased)<< as day, sum(order_total) as total FROM
`orders` GROUP BY day ORDER BY day

Any idea what to do?

View Replies !

---

Grouping?
I am quite new to sql and I don't understand why a query has a certain output.I apologise for the long post but I'd rather describe exactly what I am doing in,

I have created and populated two tables in my database that look like: ....

View Replies !

---

Grouping
I have a query that searches a database for all calls by a certain person. The database itself is actually a help desk. Within this there is a problem table, which stores all the problems, and then an actions table which is the actions taken towards that call. I want a query that will group by the problem number? But I cant seem to get this to work. This is what I have so far?

View Replies !

---

Using Max And Grouping
Want to group by clientid and show newest last_shipment_Date only.

select
cpl.last_shipment_date
from client c, client_product_link cpl
where c.id=cpl.clientid and
cpl.last_shipment_date=(select max(cpl2.last_shipment_date)
from client_product_link cpl2
where cpl.id=cpl2.id) group by cpl.clientid

Would this be the correct way of doing this? Is there a better more efficient way?

View Replies !

---

Grouping By Items
Is it possible to group items by values not defined in the database?
For example, I have a list of prices and I would like to count/group them in two groups, one > 2000 and one < 2000.
I am on MYSQL 4.0.1, so no subselects for me i'm afriad.Is this going to be possible?

View Replies !

---

Grouping Data And Sum
I am trying to get the count for date in different ranges. I used the following query:
SELECT SUM(CASE WHEN CoilLength < 50 then 1 else 0 END CASE) as '50', SUM (CASE WHEN CoilLength between 50 and 100 then 1 else 0 END CASE) as '50to100',
SUM (CASE WHEN CoilLength > 100 then 1 else 0 END CASE) as '100' from tblCoiledCoil;

I get a generic syntax error. If I use END instead of END CASE I get an error saying function myDB.SUM doesn't exist. Any ideas on how to fix it?

View Replies !

---

Grouping By Time
query = "SELECT DATE, TIME, TOTU WHERE DATE >= '2009-02-08' GROUP BY DATE, TIME";

I get several days... but for simplicity, yesterday '2009-02-08' I got an output as:

DATE TIME TOTU
2009-02-08 00:30:00 453
2009-02-08 01:00:00 296
2009-02-08 01:30:00 218
2009-02-08 02:00:00 171
2009-02-08 02:30:00 121
2009-02-08 03:00:00 86
2009-02-08 03:30:00 98
2009-02-08 04:00:00 116
2009-02-08 04:30:00 82
2009-02-08 05:00:00 62
2009-02-08 05:30:00 64
2009-02-08 06:00:00 88
2009-02-08 06:30:00 162
2009-02-08 07:00:00 274
2009-02-08 07:30:00 486
2009-02-08 08:00:00 734
2009-02-08 08:30:00 995
2009-02-08 09:00:00 1179
2009-02-08 09:30:00 1319
2009-02-08 10:00:00 1432
2009-02-08 10:30:00 1721
2009-02-08 11:00:00 1576
2009-02-08 11:30:00 1526
2009-02-08 12:00:00 1503
2009-02-08 12:30:00 1561
2009-02-08 13:00:00 1442
2009-02-08 13:30:00 1542
2009-02-08 14:00:00 1440
2009-02-08 14:30:00 1327
2009-02-08 15:00:00 1445
2009-02-08 15:30:00 1307
2009-02-08 16:00:00 1355
2009-02-08 16:30:00 1251
2009-02-08 17:00:00 1344
2009-02-08 17:30:00 1487
2009-02-08 18:00:00 1683
2009-02-08 18:30:00 1921
2009-02-08 19:00:00 2074
2009-02-08 19:30:00 1993
2009-02-08 20:00:00 2025
2009-02-08 20:30:00 2036
2009-02-08 21:00:00 1944
2009-02-08 21:30:00 1620
2009-02-08 22:00:00 1170
2009-02-08 22:30:00 952
2009-02-08 23:00:00 655
2009-02-08 23:30:00 419
2009-02-08 24:00:00 271..............

View Replies !

---

Results Grouping
I need to group products (and display them differently) that have the same 'name' in a list of search results. this application is like a shopping comparison site.

basically, i have one db that is a massive list of products from different vendors. a keyword search will then pull results and display them. for any products that come up twice (with the same 'name' but from different vendors) in the results, i want that result to be displayed differently than the single resluts.

View Replies !

---

Tables Grouping
I have problem, mybe you know how to solve it.
I would like to have table:
categoryBooks and how to make relation to table books, where one book can adhere to many categories. How to build tables to have a chance to extend number of categories and always to choose by category only these books which contains in chosen category?

View Replies !

---

Grouping Of Columns
If there be a table as below:

+----------+--------+------------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+------------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1993-03-30 | NULL |
| Puppy | Binaya | homemember | M | 1990-05-05 | 2003-06-04 |
+----------+--------+------------+------+------------+------------+


and I would like to know how many male and female are there for each species ( for eg: how many male dogs, female dogs, how many female birds and so on.

View Replies !

---

Grouping / Sorting
However the data is brought back and sorted by month and I was wondering if it's possible to bring back the data the has franid,barid in every month of the year.

So right now, as you can see showing the results for the first 3 months, Month 2 has data in the second row I don't want returned and in any calcultions. The franid, barid combo has to be represented in each and every month, in this particular case between Month 1 and Month 12.

1234, try, 7894.89, 1
1456, tre, 7842.78, 1
3526, gft, 5690.00, 1

1234, try, 6794.89, 2
6789, ght, 7842.78, 2
3526, gft, 5340.00, 2

1234, try, 2394.89, 3
1456, tre, 4542.78, 3
3526, gft, 8990.00, 3

View Replies !

---

GROUPing By More Than One Field
Thought it should be as simple as GROUP BY field1, field2, but it apparently isn't...bear with me on this complex abstraction (maybe I'm just designing my application poorly)

I have a database of authors and books in a many to many relationship. My application allows a user to click on a checkbox form...for example, if "Drama" is clicked, the application responds with a result set of all books of subject Drama.

But the application also allows you to do some filtering by author...so if you click on "Male", the application returns all male authors

The user should be able to click on "Drama" and "Male" and find all Drama books written by men.

BUT, I don't want to return just a view, but statistics as well...So I want to be able to tell the user that there are 50 books of cateogry Drama written by men in this database.

MORE SPECIFICALLY (this is my problem) I want to tell the user how many male/female authors have written dramatic books. I want the result set to be be a summary showing the number of male and female authors who ahve written dramatic books.

Lets say I have a view that contains the rows of all books of dramatic category, and this is called dramabooks...dramabooks also contains author gender of the author who wrote it.

So, in my limited MYSQL knowledge, this is the query I want to run:

SELECT COUNT(*) as numberofauthors, authorGender FROM dramabooks GROUP BY authorID, authorGender with ROLLUP";

The way I see it, you have to first group by authorID since there are multiple books per author. Then you have to group that set by gender, to get the final grouping of male and female...I'm sure this has a simple solution, but I don't know of an easier way to abstract this problem.

View Replies !

---

Grouping Elements
I was wondering if it is possible to group linked elements using SQL directly. For example, if I have some table like:

Column A | Column B | Column C | Column D
1 | 2 | A11 | A32
2 | 3 | A13 | A22
3 | 4 | A14 | A23
5 | 6 | A19 | A24
7 | 8 | A18 | A25
1 | 9 | A17 | A26

I want to group all linked items together. So in the above case my output would look something like:
Table 1:
1
2
3
4
9

Table 2:
5
6

Table 3:
7
8

or maybe something like with the above one still preferred:
1,2,3,4,9
5,6
7,8
extracted into another table...

One other question I had was, can a database have a million tables? I mean, if I had many such groups, would it be efficient to create a table for each group or is there any other approach?

View Replies !

---

GROUPing Across Tables
I want to make a pretty simple query, but I get an odd result.

Here are two tables similar to the ones I'm using:

SELECT * FROM customer;

+----+---------+
+ id | name |
+----+---------+
| 1 | Goofy |
| 2 | Donald |
| 3 | Mickey |
| 4 | Scrooge |
+----+---------+

SELECT * FROM orders;

+----+----------+
| id | customer |
+----+----------+
| 1 | 2 |
| 2 | 2 |
| 3 | 4 |
+----+----------+


Now, I want to get a list customers with the number of orders they made, including those who didn't made any, like this:

+----+---------+-----------+
+ id | name | no_orders |
+----+---------+-----------+
| 1 | Goofy | 0 |
| 2 | Donald | 2 |
| 3 | Mickey | 0 |
| 4 | Scrooge | 1 |
+----+--------+------------+

I tried this query:

SELECT customers.*,COUNT(orders.id) AS no_orders FROM customers LEFT JOIN orders ON customers.id=orders.customer GROUP BY customers.id;

But this is the result I get:

+----+---------+-----------+
+ id | name | no_orders |
+----+---------+-----------+
| 1 | Goofy | 0 |
| 2 | Donald | 2 |
| 4 | Scrooge | 1 |
+----+--------+------------+

Why Goofy is listed and Mickey isn't? Doesn't it like mice???

View Replies !

---

Grouping By Hour
I am trying to work out the hits per hour from a database
Data basically is stored as follows (with other coulombs)
idtimedate
117:30:242008/09/24
217:45:322008/09/24
318:30:502008/09/24
420:34:562008/09/24
501:34:562008/09/25
...

With the example data above I wish to be able to work out how many hits each hour has had in the past 24 hours.
Say the time is 13:00 on 2008/09/25
I would like something like this
13:00 - 0
14:00 - 0
15:00 - 0
16:00 - 0
17:00 - 2
18:00 - 1
19:00 - 0
20:00 - 1
21:00 - 0
22:00 - 0
23:00 - 0
00:00 - 0
01:00 - 1
02:00 - 0
etc to:
12:00 - 0



View Replies !

---

Simple Grouping
i need to select the latest 8 records (newest timestamp) from each of the two categories and 4 subcategories. so that the final result looks like this:

category subcategory price time
--------- ------------ ----- ---------------------
1 dogde 1.34 2005-09-08 08:13:07
1 ford 3.50 2005-09-08 10:20:12
1 toyota 2.43 2005-09-08 11:40:06
1 honda 3.00 2005-09-07 14:01:11
2 dogde 1.00 2005-09-01 10:37:00
2 ford 9.20 2005-09-08 08:23:43
2 toyota 2.55 2005-09-05 18:51:37
2 honda 6.25 2005-09-07 11:54:25

please note: there are only 2 possible categories and only 4 possible subcategories. the price should reflect that particular sale (indicated by timestamp).

View Replies !

---

Grouping By A Common Value
How can I submit several peoples details to a table at the same time, and automatically generate and store a common value for them all. eg. a group number, in order to retrieve these people as a group.

View Replies !

---

Grouping Only Top 10 Values
I am trying to figure out how to run something complex like this:

my table structure is similar to this:

player_id
player_points

each player has alot of rows with points.

The way we have our listings displayed is by doing a

SELECT player_id, SUM(player_points) as total GROUP BY player_id ORDER BY total DESC.

This works great but now we need to change our queries to reflect only top 10 scores of each player. So if a player has 50 entries for different points, we need to grab only the top 10 points and find the sum of that. Our table has over 20 million rows.

Can anyone guide me on how I would go on doing this? One way is by passing row by row into php and counting only top 10 rows. But this means going through all 20 million rows through php, very ineffective.

View Replies !

---

Ordering/grouping?
Say I have two fields of consequence in the same row for this operation, GUEST (varchar) and DATE (timestamp).

I want to make a select where all of the identical GUEST entries are grouped together, and have their DATE be the secondary ordering criteria. Additionally, I want each set of GUEST entries to have considered their highest number DATE entry to be the criteria by which to judge how high in the returned array it should appear.

little example - Note that i want the name with the highest timestamp to be first, followed by that same name with lesser timestamps (in descending order.)

sam 20041005000030
sam 20041005000025
sam 20041005000020
sam 20041005000010
dan 20041005000020
dan 20041005000015
dan 20041005000010
dan 20041005000005
ian 20041005000010
ian 20041005000005
ian 20041005000002
ian 20041005000001

I realize this might take a dash of php to get done, but I know some of you guys are pretty slick at cookin up the queries, so I figure I'll let you have a go at it. :thumbsup:

View Replies !

---

Order BEFORE Grouping
i am using this query:
SELECT sid, name FROM rso_subs WHERE pid=4 ORDER BY ln IN ("sl","en") DESC, sid GROUP BY sid

it should select each sid (id of content), where pid (id of page) is 4.
the problem is, that i want preferred languague (ln field).

View Replies !

---

Grouping Dates Together Then Sum()
Can anyone show me how to add the amounts together but by month. So all the month's of 12's added to gether and all the 11's (months).

mysql> select * from date;
+----+------------+---------+
| id | date | amount |
+----+------------+---------+
| 1 | 2006-12-25 | 100.00 |
| 2 | 2006-12-20 | 1456.34 |
| 3 | 2006-11-04 | 400.00 |
+----+------------+---------+
3 rows in set (0.00 sec)

View Replies !

---

MySQL Grouping
i am trying to generate a report that shows idle time for some vehicles... (i can do all the calcs etc but......
I am not sure if this is possible but i need to group this data by street, however vehicles often visit the same street more than once in a day and therefore cannot group those two visits together. I need to somehow group them together while the street is the same, when it changes... its a new group and so on.

DATA

This is just a sample of the data, it is produced by a query that finds records where the speed is zero.

VehicleDateTimeSpeedInstSuburbStreetLatitudeLongitudeSpeedMaxSpeedAvCourseInstCourseAv
10009/2/200706:44:440PunchbowlLUMEAH AV33.56.184S151.03.333E00106106
10009/2/200706:47:460PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200706:50:480PunchbowlLUMEAH AV33.56.184S151.03.334E00106106
10009/2/200706:53:500PunchbowlLUMEAH AV33.56.184S151.03.334E00106106
10009/2/200706:56:500PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200706:59:520PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200707:02:540PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200707:05:560PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200707:08:580PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200707:11:580PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200707:12:140PunchbowlLUMEAH AV33.56.184S151.03.334E00106106
10009/2/200707:15:200PunchbowlLUMEAH AV33.56.183S151.03.334E00106106
10009/2/200707:23:240RiverwoodKEATS AV33.56.959S151.03.158E00196196
10009/2/200707:23:340RiverwoodKEATS AV33.56.959S151.03.158E00196196
10009/2/200708:20:100BundeenaHORDERN S L34.05.037S151.08.710E00278278
10009/2/200708:30:280BundeenaBRIGHTON ST34.05.084S151.09.000E004848
10009/2/200708:36:380BundeenaBRIGHTON ST34.05.084S151.08.999E00243243
10009/2/200708:39:380BundeenaBRIGHTON ST34.05.084S151.08.999E00243243
10009/2/200708:42:400BundeenaBRIGHTON ST34.05.084S151.08.998E00243243

10009/2/200713:08:560BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:11:560BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:14:580BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:18:000BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:21:020BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:24:020BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:27:040BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:30:060BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:33:080BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:35:200BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200713:35:300BundeenaBRIGHTON ST34.05.083S151.08.998E00243243
10009/2/200714:27:000RiverwoodNETTLETON AV33.57.058S151.03.190E00286286
10009/2/200714:30:020RiverwoodNETTLETON AV33.57.058S151.03.190E00286286
10009/2/200714:33:040RiverwoodNETTLETON AV33.57.057S151.03.190E00286286
10009/2/200714:36:000RiverwoodNETTLETON AV33.57.058S151.03.190E00286286
10009/2/200714:51:460PunchbowlLUMEAH AV33.56.205S151.03.421E00273273
10009/2/200714:54:340PunchbowlLUMEAH AV33.56.204S151.03.422E00273273
10009/2/200714:56:140PunchbowlLUMEAH AV33.56.177S151.03.355E00278278
10009/2/200714:58:080PunchbowlLUMEAH AV33.56.191S151.03.341E00278278
10009/2/200714:58:120PunchbowlLUMEAH AV33.56.190S151.03.341E00278278
10009/2/200715:41:020PunchbowlLUMEAH AV33.56.190S151.03.344E00201201
10009/2/200715:41:080PunchbowlLUMEAH AV33.56.191S151.03.344E00201201
10009/2/200716:24:300PyrmontPIRRAMA RD33.52.156S151.11.771E001515
10009/2/200716:26:540PyrmontPIRRAMA RD33.52.157S151.11.763E001515
10009/2/200716:26:580PyrmontPIRRAMA RD33.52.158S151.11.763E001515
10009/2/200716:33:440PyrmontPIRRAMA RD33.52.142S151.11.766E003131
10009/2/200716:33:480PyrmontPIRRAMA RD33.52.143S151.11.766E003131

As you can see... vehicle visits LUMEAH AV twice in one day so i do't want to group these to visits together...


View Replies !

---

Grouping Age Ranges
I have a table that contains a number of entries with ages 1 to 75, I am trying to create a query so I can select the ages in intervals and count them up for each range.

So it would give me a result similar to this:

0-9: 3
10-19: 35
20-29: 12
30-39: 22
40-49: 21
50-59: 2
60+: 19

View Replies !

---

Grouping By Category
I have a bunch of articles in a table obviously called ARTICLE

It looks like this:

+---+-------------+------+------+
| ID | Category | Title | Text |
+---+-------------+------+------+
| 1 | 1 | oko | oko |
+---+-------------+------+------+
| 2 | 2 | oko | oko |
+---+-------------+------+------+
| 3 | 3 | oko | oko |
+---+-------------+------+------+
| 4 | 1 | oko | oko |
+---+-------------+------+------+
| 5 | 1 | oko | oko |
+---+-------------+------+------+
| 6 | 2 | oko | oko |
+---+-------------+------+------+
| 7 | 3 | oko | oko |
+---+-------------+------+------+
| 8 | 3 | oko | oko |
+---+-------------+------+------+

ID is auto_increment field, so article 8 is newest

I need to select two newest articles from each category, something like this:

+---+-------------+------+------+
| ID | Category | Title | Text |
+---+-------------+------+------+
| 4 | 1 | oko | oko |
+---+-------------+------+------+
| 1 | 1 | oko | oko |
+---+-------------+------+------+
| 6 | 2 | oko | oko |
+---+-------------+------+------+
| 2 | 2 | oko | oko |
+---+-------------+------+------+
| 7 | 3 | oko | oko |
+---+-------------+------+------+
| 3 | 3 | oko | oko |
+---+-------------+------+------+

(Ordered by category asc and id desc)

View Replies !

---

Count And Grouping
I need to find all 1’s in Col3 grouped by the value in Col2. I also need to know the total number of rows for the value in Col2.

Col1Col2Col3
ABC12342
ABC12342
ABC56782
ABC56781

Desired Output
eg
Col1Col2Total 1’s Total Rows (Col2)
ABC1234 0 2
ABC5678 12


View Replies !

---

Why Is It Grouping The Result Set?
i have a sql statement that finds messages and they are ordered by a date field. there is a field that is called 'hasread' to see if the message has been opened, in its original state it is NULL then it becomes a 1 when it has been opened.

i cant understand why the result set is grouping itself by the 'hasread' column so that all the read or unread messages are at the top, i am completely perplexed! .....

View Replies !

---