MYSQL query need help.

[smith randerson]

I need help on query the line of data that needed (Arrow).

 

id  a_time                            xgroup

1  2012-07-05 09:30:00  2

5  2012-07-05 09:35:00  1

6  2012-07-05 10:35:00  1

8  2012-07-05 10:35:05  1

10  2012-07-06 04:08:00  2

15  2012-07-06 09:10:00  2

18  2012-07-06 12:45:00  1

21  2012-07-06 13:15:00  2

23  2012-07-06 14:00:00  1

30  2012-07-06 08:00:00  2  <--

33  2012-07-07 02:45:00  2

35  2012-07-07 04:25:03  2

40  2012-07-07 11:10:06  2

 

Hi, I hope someone could help me how to query the row of data from above table.

I need to query the line of data which highlighted with an ARROW.

 

Please note that the ID is unique(Primary KeY), but for reason which data which is inserted with "INSERT IGNORE", the id will not align in sequence, thus ID will be jumped.

As from the data table, there will be several group categories(column xGROUP) as 1, 2, 3 .....

data from column "a_time" will be always collected as ascending because new data that collected will always the newest time ahead.

 

Assume that i need to call out what is the id where the 'last categories' was insert into table(from the table showed, the last categories is 2), and the 1st ID from the categories which last insert categories is.

 

I hope the structure of the table (arrow) and the sentences that i elaborate makes you understand what i wanted.

 

Please help me how to query it out.

[Pikachu2000]

I'm sorry, but that makes little sense.

[smith randerson]

What do you mean by "That makes little sense"?

[Pikachu2000]

Your question is gobbledygook; not understandable; needs clarification.

[smith randerson]

How to query the data which the arrow pointed out?

 

The structure showed is the data entry that i collected on MYSQL.

 

There is 3 column, id | atime | xgroup.

 

[Pikachu2000]

The only way your question can be answered since you haven't provided adequate information, such as the criteria involved in selecting this particular record, is this:

 

SELECT id, atime, xgroup FROM [YOUR_TABLE_NAME_GOES_HERE] WHERE id = 30 AND atime = '2012-07-06 08:00:00' AND xgroup = 2

 

But that's completely useless because there's no point querying a database table only to get information you already have.

[Barand]

The ID is a unique identifier, not a sequence number. You application should work just the same even if IDs were generated as a random number between 1 and 4 billion whenever you inserted a new row. As I have said before, I wish this were the case to stop  people getting hung up on gaps between ID numbers.

 

That said, what is significant about id=30 - it seems to be the 3rd row entered for xgroup 2 according to the timestamps?

[hakimserwa]

I think one thing that you must be sure of is, the id field is it auto generated as auto increment or its inserted by your users. if its auto generated then it doesnt matter where it is just refer to it in your sql it will call the right information as prefered. remember if the id is auto crteated, it will only be created once and that means there will only be 1 id for each record. ids not being in sequence might be a result of delting some records in your database so when you insert new records, the database then goesback and looks for those empty or reserved place and fills them with the new records because at that time it cant change the ids for the new record to be the id of the records that your deleted. so each new record will be asigned anew id

 

"SELECT * FROM  yourtabel WHERE id=30 " that will give yopu any record with and id value of 30.