Dear All

Please help to write a SQL to find our the last goods receipts for each type of goods.

table field "
   Product
   GRN
   Rec Date
   Rec QTY

Date may like this

Apple   GRN001   31/1/2008  100.00
Mango  GRN001   31/1/2008  50
Apple   GRN002   01/02/2008 10.

Last good receipts?  I think your question is not specific enough.    What exactly do you want to show in the query result?  And what you don't want to see?  And you want the result to be sorted by GRN?

It might look something like this:

Assuming u mean "Apple", "Mango" are types of goods and date is in Date datatype and u want to output GRN, which I assume is the recipt#, for each type of Product
Then,

SELECT Product, GRN FROM table t1, (SELECT Product, MAX(Date) AS Date FROM table GROUP BY Product) t2
WHERE t1.Product = t2.Product AND t1.Date = t2.Date

the subquery in bracket, t2:
"GROUP BY Product" groups all the same type of goods
"SELECT Product, MAX(Date) finds the latest record date for each type of goods we grouped.

then to find the GRN of the latest record date of each type of goods, we query the table again with the restrictions product=product & date=date, which is natural join t1 with t2.

u can also do it with ORDER BY, but either way I believe you will still need to use subqueries or more than one query given my assumptions to ur questions are correct

Thanks your help

May be my question is not good, let me save more.

In table GRN have following field

date, GRN Number, Product, Qty , Amt

The samples may like this.
1/1/08,  GRN01, Apple, 100, $5
1/2/08,  GRN02, Mango, 10, $25    <-
1/20/08, GRN03, Apple, 10, $5       <-

I want to get the Result is :-
1/20/08, GRN03, Apple, 10, $5
1/2/08, GRN02, Mango, 10,25


What is the seript.

Oh.. then numbie's SQL should work!  But if it doesn't, try the following syntax, it is the same idea.  

SELECT t1.*
   FROM yourtable t1
           INNER JOIN
           (SELECT MAX(date) as maxDate, product
               FROM yourtable GROUP BY product) t2
           ON t1.date = t2.maxDate
    AND t1.product = t2.product
ORDER BY t1.product

What i suggest you is the alter your table structure,
add in an auto increment index as primary key,

that would helps alot in searching and editing purpose.

not necessary,
in fact, i highly recommend you not to add an increment index according to your table structure

index does not guarentee performance enhancement, in fact they usually make query slower when table is small. Index is only useful when you know certain tuples will most of the time be searched first. If searches are pretty random then indexes will most likely hinder performance.
Even if index is desired, setting index on an irrelevant incremental value would most likely not help at all as that will not determine which tuples will be more frequently visited and may result slower queries.

In your table, i believe GRN Number and Product will defines the uniqueness, thus, you can just set those two as primary and you can set indexes to whatever after detail analysis on ur table performance, but only if u actaully need it.
if GRN Number and Product does not give uniqueness, u can add column filled with incremental values as something call transaction# or whatever, but do not set that as index.
editing purpose-wise, incremental index does not help at all as it is an irrelevant field on ur table, and will result slower insertion time.

I suggest u only use index when u absolutely no what u ar doing, becoz index is very costly, they take up quite a lot of space when database is large, you don't want to trade 500mb for just a 1sec boost performance. Also, don't forget indexes also increases compilation time greatly, so don't consider using it unless u can greatly benefit from it

any inputs mickey? u seems to no database quite a bit

As what you commented,
Indexing does not help in while the table is small,
Look at the data provided by tatacat, it's a receipt table, would you expect receipt table just to store few records?
It's impossible, I would say it could be few million records in the future.

We've solve a lot problems of slow queries with adding in indexing, and the Improvement of using indexing giving us more than 90% which is generated from MySQL analyst report.

you could use EXPLAIN before doing any SELECT queries and refer to "rows" column, which shows how many rows that this queries need to Search along and output your result.

Do it twice, with and without indexing. I will guarantee the results is very tremendous.

Yes, you could add GRN column as an index, which i can see here, the data type of this column is VARCHAR.
"GRN" this 3 character already consume 3-4 bytes, which the following integer behind is not in count yet.

An UNSIGNED mediumint which could use up to 16777214 records, every rows just consume 3 bytes.

I find no reason of storing GRN this 3 character into database. and as what i see tatacat provided, GRN01, GRN02, GRN03..
the number is incremental, why dont make use of auto increment that provided? what if there is duplicated record added in at the sametime? this could be possible as well

"Indexing does not help in while the table is small..."
not only that, index doesn't even help unless u no what u ar doing, indexes are just pointers that re-order the records, since we don't no what queries will be performed, we cannot determine which index will benefit, choosing wrong index will make bad ordering and cause slower performance, thus indexes should not be implemented until u no queries actually bias towards certain tuple, but that may not always happen, often, it will be pretty random and that case, indexes will most likely not help.

"Look at the data provided by tatacat, it's a receipt table..."
apparently, in database, we don't usually consider receipt table like the one provided by tatacat as a large table, because no matter how many records are in there, it's still O(n) to achieve any record, table is a small table
well, it doesn't really matter, the difference isn't big, but the general point is that indexes should not be used when it's not required, u can see that database tables in workplace are usually created without indexes, and they add indexes to certain tables when queries are slow

"we've solve a lot problems of slow queries with adding in indexing..."
well, a slow query kinda implies that index will help, of coz it gives u improvement, but tatacat's query was never slow (i would even argue that according to her structure, even millions of records should only takes seconds). Try adding index to one of ur very fast query and see if u insist index gets u faster
there is reason why companies do index adding process and not initialize them at the beginning when creating the tables

"I find no reason of storing GRN this 3 character into database..."
it's true that just using digits are better, but really now, we don't really have to be that picky, this is not a critical thing, I could imagine this suggestion will be prioritized to the last item in a workplace.

"why dont make use of auto increment that provided?"
well, from what I see according to the information tatacat gave, there can be two GRN01 record, apple and mango,
thus it's not incremental, it could be 1,1,1,1,2,3,3,4 for GRN.
and as i said GRN Number and Product will defines the primary key to prevent duplications, and if GRN Number and Product infact do not provide uniqueness, as i said, u can create incremental key, just that do not make that column as an index alone.

This seems to be an interesting topic,
I found a table with 5 fields in my companies's database with very similar stucture as tatacat's. since i have nothing to do currently, i will duplicate that table do some testing on it.

Table info
Fields: 5 (INT, Date, VARCHAR, VARCHAR, INT)
Records: 1,322,281
Size: 62MB
Index: no
Primary key: INT, VARCHAR
Query to be performed:

                        SELECT *
                        FROM table t1,
                                (SELECT VARCHAR, MAX(Date) AS Date
                                 FROM table
                                 GROUP BY VARCHAR) t2
                        WHERE t1.VARCHAR = t2.VARCHAR
                            AND t1.Date = t2.Date

Records Return: 20,188



PERFORMANCE TEST
-------------------------------------------------------

Original table (non-index)
Size: 62MB
Query Speed Test 1: 2.1sec
Query Speed Test 2: 1.9sec
Query Speed Test 3: 2.2sec


Post results under here later... UPDATED


Adding incremental index column
Size: 72MB
Query Speed Test 1: 3.4sec
Query Speed Test 2: 3.1sec
Query Speed Test 3: 3.2sec

Size performance: -16.1%
Average Speed Performance: -75.7%


no incremental column, choose appropriate field as index
Size: 80MB
Query Speed Test 1: 2.2sec
Query Speed Test 2: 2.1sec
Query Speed Test 3: 2.1sec

Size performance: -29.0%
Average Speed Performance: -3.2%


add incremental column, choose appropriate field as index
Size: 85MB
Query Speed Test 1: 2.1sec
Query Speed Test 2: 2.2sec
Query Speed Test 3: 2.2sec

Size performance: -37.1%
Average Speed Performance: -4.8%


no incremental column, choose index benefiting the query being tested
Size: 80MB
Query Speed Test 1: 2.1sec
Query Speed Test 2: 2.0sec
Query Speed Test 3: 1.9sec

Size performance: -29.0%
Average Speed Performance: +3.2%


add incremental column, choose index benefiting the query being tested
Size: 85MB
Query Speed Test 1: 2.0sec
Query Speed Test 2: 2.0sec
Query Speed Test 3: 2.1sec

Size performance: -37.1%
Average Speed Performance: +1.6%